Subject: plpgsql select into

From: Roger Mason <[email-masked]>
Date: Fri, 20 Aug 2021 08:08:07 -0230

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

---

From: Tom Lane <[email-masked]>
Date: Fri, 20 Aug 2021 10:12:48 -0400

>       regexp_split_to_table(info_out, '\n') AS info

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

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 20 Aug 2021 16:13:47 +0200

> 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

---

From: Roger Mason <[email-masked]>
Date: Fri, 20 Aug 2021 13:04:20 -0230

Hello Tom & Laurenz,

Thanks for your responses.

> 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

---

From: Tom Lane <[email-masked]>
Date: Fri, 20 Aug 2021 12:07:40 -0400

> test=> select * from info_table ;

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

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 20 Aug 2021 09:12:03 -0700

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

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.

David J.

---

From: Roger Mason <[email-masked]>
Date: Sat, 21 Aug 2021 08:22:28 -0230

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

Embarassed cough :-).

And thanks for the simplification.

Roger

---

From: Roger Mason <[email-masked]>
Date: Sat, 21 Aug 2021 08:24:21 -0230

> plpgsql feature of the same form.

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

Roger