postgresql - Concatenate result of a SELECT into an EXECUTE

Question

I am trying to recreate a view from a backup of the definition using EXECUTE in an anonymous code block (DO $$ ... BEGIN... END$$), but I cannot get it to work. This is approximately what I have so far:

EXECUTE 'CREATE VIEW image AS (' || (SELECT definition FROM tmp_view_backup WHERE viewname = 'image') || ')';

The table with the backup of the view definitions was made like this

CREATE TABLE  tmp_view_backup
AS
(select viewname,definition from pg_views
WHERE schemaname = 'public'
);

It seems like it is the select that causes me some problems in creating the string, as the result is not at all what I anticipated:

postgres=# select char_length('CREATE VIEW image AS ('  || (SELECT definition FROM tmp_view_backup WHERE viewname = 'image') || ')') as f ;
 f 
---

(1 row)

What is this? An empty row? If I drop the select I get a normal answer (like the string length being 23).

标签： postgresqlpsql

Answer 1

You can make use of PSQL \gexec parameter.

SELECT format('CREATE VIEW image AS  %s ', definition)
FROM tmp_view_backup 
WHERE viewname = 'image';\gexec  

Or in a DO block

DO $$
BEGIN

EXECUTE format('CREATE VIEW image AS  %s ', definition)
FROM  tmp_view_backup 
WHERE viewname = 'image';
END $$;