Thread: Problem with the semantics of "select into" in a plpgsql function
I have a plpython function that returns a set of records. I loop over them to insert them into a temp table created by another function.
I wanted to test
select into temp_eav_table (column) select a.column from tbl as a where....
approach to see if it performs better than the loop. However, I'm not able to compile the function due to an error that says "temp_eav_table is not a known variable"
So the context assumes this is supposed to be a variable. If I try execute '...', then I have trouble passing a bytea parameter to the python function. This is what I have at the moment:
SELECT INTO temp_eav_table (valstring,
featuremappingid,
featurename,
rmtypename,
actualrmtypename,
path,
pathstring)
select selected_node.valstring,
selected_node.featuremappingid,
selected_node.featurename,
selected_node.rmtypename,
selected_node.actualrmtypename,
selected_node.path,
selected_node.pathstring
from py_get_eav_rows_from_pb(payload ) as selected_node;
any thoughts?
Best regards
Seref
The documentation for "select into" covers this and provides your alternatives. http://www.postgresql.org/docs/9.2/interactive/sql-selectinto.html David J. On Dec 25, 2012, at 10:20, Seref Arikan <serefarikan@kurumsalteknoloji.com> wrote: > Greetings, > I have a plpython function that returns a set of records. I loop over them to insert them into a temp table created byanother function. > I wanted to test > select into temp_eav_table (column) select a.column from tbl as a where.... > approach to see if it performs better than the loop. However, I'm not able to compile the function due to an error thatsays "temp_eav_table is not a known variable" > > So the context assumes this is supposed to be a variable. If I try execute '...', then I have trouble passing a bytea parameterto the python function. This is what I have at the moment: > > SELECT INTO temp_eav_table (valstring, > featuremappingid, > featurename, > rmtypename, > actualrmtypename, > path, > pathstring) > select selected_node.valstring, > selected_node.featuremappingid, > selected_node.featurename, > selected_node.rmtypename, > selected_node.actualrmtypename, > selected_node.path, > selected_node.pathstring > from py_get_eav_rows_from_pb(payload ) as selected_node; > > any thoughts? > > Best regards > Seref >
Thanks for your response. However, I can't see my solution there. According to documentation, select into is supposed to create a new table. "Create table as " is offered as an alternative, but my table is already ready when I execute the statement.
Maybe it is my lack of understanding, but I can't see how the documentation helps to use eav_temp_table as a table name, rather than a variable.
The documentation for "select into" covers this and provides your alternatives.
http://www.postgresql.org/docs/9.2/interactive/sql-selectinto.html
David J.
On Dec 25, 2012, at 10:20, Seref Arikan <serefarikan@kurumsalteknoloji.com> wrote:
> Greetings,
> I have a plpython function that returns a set of records. I loop over them to insert them into a temp table created by another function.
> I wanted to test
> select into temp_eav_table (column) select a.column from tbl as a where....
> approach to see if it performs better than the loop. However, I'm not able to compile the function due to an error that says "temp_eav_table is not a known variable"
>
> So the context assumes this is supposed to be a variable. If I try execute '...', then I have trouble passing a bytea parameter to the python function. This is what I have at the moment:
>
> SELECT INTO temp_eav_table (valstring,
> featuremappingid,
> featurename,
> rmtypename,
> actualrmtypename,
> path,
> pathstring)
> select selected_node.valstring,
> selected_node.featuremappingid,
> selected_node.featurename,
> selected_node.rmtypename,
> selected_node.actualrmtypename,
> selected_node.path,
> selected_node.pathstring
> from py_get_eav_rows_from_pb(payload ) as selected_node;
>
> any thoughts?
>
> Best regards
> Seref
>
“SELECT INTO -- define a new table from the results of a query”
If the table exists use:
INSERT INTO tbl (…) SELECT …
David J.
From: Seref Arikan [mailto:serefarikan@kurumsalteknoloji.com]
Sent: Tuesday, December 25, 2012 10:52 AM
To: David Johnston
Cc: PG-General Mailing List
Subject: Re: [GENERAL] Problem with the semantics of "select into" in a plpgsql function
David,
Thanks for your response. However, I can't see my solution there. According to documentation, select into is supposed to create a new table. "Create table as " is offered as an alternative, but my table is already ready when I execute the statement.
Maybe it is my lack of understanding, but I can't see how the documentation helps to use eav_temp_table as a table name, rather than a variable.
On Tue, Dec 25, 2012 at 3:39 PM, David Johnston <polobo@yahoo.com> wrote:
The documentation for "select into" covers this and provides your alternatives.
http://www.postgresql.org/docs/9.2/interactive/sql-selectinto.html
David J.
On Dec 25, 2012, at 10:20, Seref Arikan <serefarikan@kurumsalteknoloji.com> wrote:
> Greetings,
> I have a plpython function that returns a set of records. I loop over them to insert them into a temp table created by another function.
> I wanted to test
> select into temp_eav_table (column) select a.column from tbl as a where....
> approach to see if it performs better than the loop. However, I'm not able to compile the function due to an error that says "temp_eav_table is not a known variable"
>
> So the context assumes this is supposed to be a variable. If I try execute '...', then I have trouble passing a bytea parameter to the python function. This is what I have at the moment:
>
> SELECT INTO temp_eav_table (valstring,
> featuremappingid,
> featurename,
> rmtypename,
> actualrmtypename,
> path,
> pathstring)
> select selected_node.valstring,
> selected_node.featuremappingid,
> selected_node.featurename,
> selected_node.rmtypename,
> selected_node.actualrmtypename,
> selected_node.path,
> selected_node.pathstring
> from py_get_eav_rows_from_pb(payload ) as selected_node;
>
> any thoughts?
>
> Best regards
> Seref
>
On 12/25/2012 07:20 AM, Seref Arikan wrote: > Greetings, > I have a plpython function that returns a set of records. I loop over > them to insert them into a temp table created by another function. > I wanted to test > select into temp_eav_table (column) select a.column from tbl as a where.... > approach to see if it performs better than the loop. However, I'm not > able to compile the function due to an error that says "temp_eav_table > is not a known variable" Probably going to have to show the actual plpython code. The message looks like you used temp_eav_table as a variable before defining it. > > So the context assumes this is supposed to be a variable. If I try > execute '...', then I have trouble passing a bytea parameter to the > python function. This is what I have at the moment: > > SELECT INTO temp_eav_table (valstring, > featuremappingid, > featurename, > rmtypename, > actualrmtypename, > path, > pathstring) > select selected_node.valstring, > selected_node.featuremappingid, > selected_node.featurename, > selected_node.rmtypename, > selected_node.actualrmtypename, > selected_node.path, > selected_node.pathstring > from py_get_eav_rows_from_pb(payload ) as selected_node; > > any thoughts? I think you may be reading the plpgsql docs. The SELECT INTO syntax there only exists within plpgsql. You are using plpythonu and it will not work there. It follows the syntax here: http://www.postgresql.org/docs/9.2/interactive/sql-selectinto.html > > Best regards > Seref > -- Adrian Klaver adrian.klaver@gmail.com
Seref Arikan <serefarikan@kurumsalteknoloji.com> writes:
> I have a plpython function that returns a set of records.
Is that actually plpython, or plpgsql? Because what you're showing is
not legal syntax in either bare SQL or plpython, but it would act as
you're reporting in plpgsql:
> SELECT INTO temp_eav_table (valstring,
> featuremappingid,
> featurename,
> rmtypename,
> actualrmtypename,
> path,
> pathstring)
> select selected_node.valstring,
> selected_node.featuremappingid,
> selected_node.featurename,
> selected_node.rmtypename,
> selected_node.actualrmtypename,
> selected_node.path,
> selected_node.pathstring
> from py_get_eav_rows_from_pb(payload ) as selected_node;
SELECT INTO in plpgsql is a completely different construct than SELECT
INTO in bare SQL: the INTO target is always a local variable of the
function. You should use CREATE TABLE AS to get the effect you're
after. This is covered in the docs page David pointed you to, as
well as in the plpgsql documentation.
regards, tom lane
Following David's advice, I've used insert into syntax, but got hit with a report of ambiguity of selected node.
The issue was discussed here: http://archives.postgresql.org/pgsql-sql/2011-09/msg00059.php
The advice here: http://www.postgresql.org/docs/9.0/static/plpgsql-implementation.html helped me remove the ambiguity, and an insertion of about 900 rows is working 25% or so faster if I remove the loop and use this approach.
To clarify: I have a few functions used by a top level function, and one of these is a plpython function that processes a binary blob and creates rows which are inserted into a temp table (created by the top level function). I was trying to make this particular insert faster, which seems to have worked.
So everything is going on in a top level pgplsql function with calls to plpython functions at various locations.
Regards
Seref
ps: I am really not sure if there is any other mail list out there that would respond to a technical question with such helpful input on a Christmas day. Thanks guys, you rock!
Seref Arikan <serefarikan@kurumsalteknoloji.com> writes:Is that actually plpython, or plpgsql? Because what you're showing is
> I have a plpython function that returns a set of records.
not legal syntax in either bare SQL or plpython, but it would act as
you're reporting in plpgsql:SELECT INTO in plpgsql is a completely different construct than SELECT
> SELECT INTO temp_eav_table (valstring,
> featuremappingid,
> featurename,
> rmtypename,
> actualrmtypename,
> path,
> pathstring)
> select selected_node.valstring,
> selected_node.featuremappingid,
> selected_node.featurename,
> selected_node.rmtypename,
> selected_node.actualrmtypename,
> selected_node.path,
> selected_node.pathstring
> from py_get_eav_rows_from_pb(payload ) as selected_node;
INTO in bare SQL: the INTO target is always a local variable of the
function. You should use CREATE TABLE AS to get the effect you're
after. This is covered in the docs page David pointed you to, as
well as in the plpgsql documentation.
regards, tom lane