create temp view from function inside plpgsql function.

Hello

2014-04-04 11:43 GMT+02:00 Tjibbe <tjibbe@rijpma.org>:

I have a plpgsql function with:

PERFORM * FROM answers(_h); --works fine.
CREATE TEMP VIEW answers AS SELECT * FROM answers(_h); --gives error...

Inside view definition should not be plpgsql variable - this statement has
no plan - CREATE VIEW

probably you can do with dynamic SQL

EXECUTE 'CREATE TEMP VIEW answers AS SELECT * FROM answers(' ||
quote_literal(_h) || ')';

Regards

Pavel Stehule

Thanks that works!

Little bit confusing ERROR.

Regards

Tjibbe

--
+31 6 29401726
tjibbe@rijpma.org
Jacobusstraat 185
3012 JM Rotterdam

On 4 April 2014 11:43, Tjibbe <tjibbe@rijpma.org> wrote:

2014-04-04 14:16 GMT+02:00 Tjibbe <tjibbe@rijpma.org>:

Thanks that works!

Little bit confusing ERROR.

yes, it could be - but hard to fix it, because it is based on cooperation
two worlds - plpgsql and SQL - and it is sometimes not simple.

When you understand how plpgsql interpret use variables in SQL queries,
then you understand to this message. Simple rule - never use plpgsql
variables in DDL

Regards

Pavel Stehule

Re-posting, see quote

David Johnston wrote

Pavel Stehule wrote

2014-04-04 14:16 GMT+02:00 Tjibbe &lt;

tjibbe@

&gt;:

Thanks that works!

Little bit confusing ERROR.

yes, it could be - but hard to fix it, because it is based on cooperation
two worlds - plpgsql and SQL - and it is sometimes not simple.

When you understand how plpgsql interpret use variables in SQL queries,
then you understand to this message. Simple rule - never use plpgsql
variables in DDL

Yet, IIRC, if you had done a CREATE TEMP TABLE instead of view the query
would have worked just fine. The issue is with CREATE VIEW specifically
because the query itself is part of the final content whereas for CREATE
TABLE the query is only used to generate the data which is then stored.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/create-temp-view-from-function-inside-plpgsql-function-tp5798658p5799286.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general