Обсуждение: plpgsql select into

Hello,

I want to take a multiline text column from one table, split it into
rows and insert it into another table.  Eventually this function will
also need insert the 'id' field and a timestamp into the other table but
for now I'm focused on dealing with the multiline text.  I could code
all this up in C++ but I'm doing it as a plpgsql function so that the
function can be called from a trigger when the data are inserted into
the database.  So far an exercise in frustration.  Here is may latest
effort:

CREATE OR REPLACE FUNCTION get_info (id text)
  RETURNS TABLE (
    tabular_info text
  )
  AS $function$
BEGIN
  RETURN query WITH a AS (
    SELECT
      regexp_split_to_table(info_out, '\n') AS info
    FROM
      public.results
    WHERE
      public.results.jid = id
)
  SELECT
    * INTO tabular_info
  FROM
    a RETURN;
END;
$function$
LANGUAGE plpgsql;

I execute the function:

select get_info('1043_1');

ERROR:  cannot open SELECT query as cursor
CONTEXT:  PL/pgSQL function get_info(text) line 3 at RETURN QUERY

Perhaps what I'm trying to do is impossible, in which case it would be
useful to know if a trigger can be set up to call out to an external
command.

I appreciate any help offered.

Thanks,
Roger

Roger Mason <rmason@mun.ca> writes:
> CREATE OR REPLACE FUNCTION get_info (id text)
>   RETURNS TABLE (
>     tabular_info text
>   )
>   AS $function$
> BEGIN
>   RETURN query WITH a AS (
>     SELECT
>       regexp_split_to_table(info_out, '\n') AS info
>     FROM
>       public.results
>     WHERE
>       public.results.jid = id
> )
>   SELECT
>     * INTO tabular_info
>   FROM
>     a RETURN;
> END;
> $function$
> LANGUAGE plpgsql;

You need to drop the "INTO tabular_info" bit, as the RETURN QUERY
context already dictates where the results should go.  Possibly
we could improve the error message.  It's already been changed
somewhat in v14/HEAD: I get

ERROR:  query "WITH a AS (
    SELECT
      regexp_split_to_table(info_out, '\n') AS info
    FROM
      public.results
    WHERE
      public.results.jid = id
)
  SELECT
    * INTO tabular_info
  FROM
    a RETURN" is not a SELECT
CONTEXT:  PL/pgSQL function get_info(text) line 3 at RETURN QUERY

Looking at this, though, I'm pretty unhappy with it.  It would be
more readable to put the query text last, or maybe even as a
CONTEXT line.  But the real issue is that it's still not making
the point that SELECT INTO is different from plain SELECT.
Perhaps we should special-case that, with say "query is SELECT INTO,
but it should be a plain SELECT".

            regards, tom lane

On Fri, 2021-08-20 at 08:08 -0230, Roger Mason wrote:
> CREATE OR REPLACE FUNCTION get_info (id text)
>   RETURNS TABLE (
>     tabular_info text
>   )
>   AS $function$
> BEGIN
>   RETURN query WITH a AS (
>     SELECT
>       regexp_split_to_table(info_out, '\n') AS info
>     FROM
>       public.results
>     WHERE
>       public.results.jid = id
> )
>   SELECT
>     * INTO tabular_info
>   FROM
>     a RETURN;
> END;
> $function$
> LANGUAGE plpgsql;
> 
> I execute the function:
> 
> select get_info('1043_1');
> 
> ERROR:  cannot open SELECT query as cursor
> CONTEXT:  PL/pgSQL function get_info(text) line 3 at RETURN QUERY

Omit "INTO tabular_info" from the query.
RETURN QUERY already is a destination for the query result.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Hello Tom & Laurenz,

Thanks for your responses.

Laurenz Albe writes:

> Omit "INTO tabular_info" from the query.
> RETURN QUERY already is a destination for the query result.

Yes, if I omit the INTO clause I get data returned.  So then how do I
insert the result of the plpgsql call into a table?  This is what
happens if I try calling 'select into' in psql:

select into info_table from (select get_info('1043_1')) as info_split;
SELECT 1288
test=> select * from info_table ;
--
(1288 rows)

How do I access the data in 'info_table'?

Thanks,
Roger

Roger Mason <rmason@mun.ca> writes:
> Yes, if I omit the INTO clause I get data returned.  So then how do I
> insert the result of the plpgsql call into a table?  This is what
> happens if I try calling 'select into' in psql:

> select into info_table from (select get_info('1043_1')) as info_split;
> SELECT 1288
> test=> select * from info_table ;
> --
> (1288 rows)

You selected zero columns (which psql is not very good at displaying :-().
Try

select * into info_table from (select get_info('1043_1')) as info_split;

BTW, that could be simplified a lot:

select * into info_table from get_info('1043_1');

            regards, tom lane

Tom Lane writes:

> Roger Mason <rmason@mun.ca> writes:
> You selected zero columns (which psql is not very good at displaying :-().
> Try
>
> select * into info_table from (select get_info('1043_1')) as info_split;
>
> BTW, that could be simplified a lot:
>
> select * into info_table from get_info('1043_1');

Embarassed cough :-).

And thanks for the simplification.

Roger

David G. Johnston writes:

> I suggest you should pretend that SQL's "SELECT INTO" doesn't exist.  If
> you want to create a table from a result write: "CREATE TABLE AS" and then
> a normal select query.  In particular the entire "action" command is kept
> whole instead of needing to put a bunch of column names in between the
> "SELECT" and the "INTO" - and you also are less likely to confuse the
> plpgsql feature of the same form.

Thank you David.  I will try to heed that advice.

Roger