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

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.htmlhelped
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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Seref Arikan <serefarikan(at)kurumsalteknoloji(dot)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
>