Create view in Cloud Spanner only if the source table is non-empty

Question

I want to be able to create a Spanner view only if the source table contains at least one record. If not I want the view creation to fail. For instance, this is my view statement without any non-empty check:

CREATE OR REPLACE VIEW user_view
SQL SECURITY INVOKER
AS SELECT 
    user.name as name,
    user.lastname as lastname
  FROM user

Thanks

Answer 1

What you are asking is not directly possible. I can think of a couple of alternatives:

1. If you VIEW definition is generated from code, then you could adjust your generation function in such a way that it automatically generates an invalid syntax if the source table is empty. But it would in that case probably be easier/cleaner to just not generate the view in the first place.
2. What is possible is to create a view that cannot be used if the source table is empty. That is; The view will be created and visible in the database, but it will return an error whenever someone tries to query it. You can achieve that with a view definition like this:

create or replace view v_test
   sql security invoker as
select case count(1)
   when 0 then cast(cast(current_timestamp() as varchar) as bool)
   else false
end as empty
from (select 1 from user limit 1) t

The trick in the above query is that the current_timestamp() function is non-deterministic, but it will never return a value that can actually be cast to a bool. This means that the query can be parsed and is accepted as a valid query for the VIEW definition. The cast will only be invoked if the source table is empty.