Thread: create temp view from function inside plpgsql function.
I have a plpgsql function with:
PERFORM * FROM answers(_h); --works fine.
CREATE TEMP VIEW answers AS SELECT * FROM answers(_h); --gives error...
Why I get this error:
ERROR: column \"_h\" does not exist\nLINE 1: ...TEMP VIEW answers AS SELECT * FROM antwoorden_view(_h)
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
Why I get this error:ERROR: column \"_h\" does not exist\nLINE 1: ...TEMP VIEW answers AS SELECT * FROM antwoorden_view(_h)
Thanks that works!
Tjibbe
Little bit confusing ERROR.
Regards
Tjibbe
On 4 April 2014 11:43, Tjibbe <tjibbe@rijpma.org> wrote:
I have a plpgsql function with:PERFORM * FROM answers(_h); --works fine.CREATE TEMP VIEW answers AS SELECT * FROM answers(_h); --gives error...Why I get this error:ERROR: column \"_h\" does not exist\nLINE 1: ...TEMP VIEW answers AS SELECT * FROM antwoorden_view(_h)
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
Pavel Stehule
Regards
TjibbeOn 4 April 2014 11:43, Tjibbe <tjibbe@rijpma.org> wrote:I have a plpgsql function with:PERFORM * FROM answers(_h); --works fine.CREATE TEMP VIEW answers AS SELECT * FROM answers(_h); --gives error...Why I get this error:ERROR: column \"_h\" does not exist\nLINE 1: ...TEMP VIEW answers AS SELECT * FROM antwoorden_view(_h)
Re-posting, see quote David Johnston wrote > > Pavel Stehule wrote >> 2014-04-04 14:16 GMT+02:00 Tjibbe < >> tjibbe@ >> >: >> >>> 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.