Search Postgresql Archives

Re: Problem with the semantics of "select into" in a plpgsql function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks Tom,
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!


On Tue, Dec 25, 2012 at 6:37 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Seref Arikan <serefarikan@xxxxxxxxxxxxxxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux