public inbox for [email protected]  

help / color / mirror / Atom feed

plpgsql select into
8+ messages / 4 participants
[nested] [flat]

* plpgsql select into
@ 2021-08-20 10:38  Roger Mason <[email protected]>
  0 siblings, 2 replies; 8+ messages in thread

From: Roger Mason @ 2021-08-20 10:38 UTC (permalink / raw)
  To: pgsql-novice

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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: plpgsql select into
@ 2021-08-20 14:12  Tom Lane <[email protected]>
  parent: Roger Mason <[email protected]>
  1 sibling, 0 replies; 8+ messages in thread

From: Tom Lane @ 2021-08-20 14:12 UTC (permalink / raw)
  To: Roger Mason <[email protected]>; +Cc: pgsql-novice

Roger Mason <[email protected]> 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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: plpgsql select into
@ 2021-08-20 14:13  Laurenz Albe <[email protected]>
  parent: Roger Mason <[email protected]>
  1 sibling, 1 reply; 8+ messages in thread

From: Laurenz Albe @ 2021-08-20 14:13 UTC (permalink / raw)
  To: Roger Mason <[email protected]>; pgsql-novice

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






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: plpgsql select into
@ 2021-08-20 15:34  Roger Mason <[email protected]>
  parent: Laurenz Albe <[email protected]>
  0 siblings, 2 replies; 8+ messages in thread

From: Roger Mason @ 2021-08-20 15:34 UTC (permalink / raw)
  To: ; +Cc: pgsql-novice

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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: plpgsql select into
@ 2021-08-20 16:07  Tom Lane <[email protected]>
  parent: Roger Mason <[email protected]>
  1 sibling, 1 reply; 8+ messages in thread

From: Tom Lane @ 2021-08-20 16:07 UTC (permalink / raw)
  To: Roger Mason <[email protected]>; +Cc: pgsql-novice

Roger Mason <[email protected]> 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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: plpgsql select into
@ 2021-08-20 16:12  David G. Johnston <[email protected]>
  parent: Roger Mason <[email protected]>
  1 sibling, 1 reply; 8+ messages in thread

From: David G. Johnston @ 2021-08-20 16:12 UTC (permalink / raw)
  To: Roger Mason <[email protected]>; +Cc: pgsql-novice

On Fri, Aug 20, 2021 at 8:51 AM Roger Mason <[email protected]> wrote:

> 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.


^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: plpgsql select into
@ 2021-08-21 10:52  Roger Mason <[email protected]>
  parent: Tom Lane <[email protected]>
  0 siblings, 0 replies; 8+ messages in thread

From: Roger Mason @ 2021-08-21 10:52 UTC (permalink / raw)
  To: pgsql-novice


Tom Lane writes:

> Roger Mason <[email protected]> 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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: plpgsql select into
@ 2021-08-21 10:54  Roger Mason <[email protected]>
  parent: David G. Johnston <[email protected]>
  0 siblings, 0 replies; 8+ messages in thread

From: Roger Mason @ 2021-08-21 10:54 UTC (permalink / raw)
  To: pgsql-novice


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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

end of thread, other threads:[~2021-08-21 10:54 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-08-20 10:38 plpgsql select into Roger Mason <[email protected]>
2021-08-20 14:12 ` Tom Lane <[email protected]>
2021-08-20 14:13 ` Laurenz Albe <[email protected]>
2021-08-20 15:34   ` Roger Mason <[email protected]>
2021-08-20 16:07     ` Tom Lane <[email protected]>
2021-08-21 10:52       ` Roger Mason <[email protected]>
2021-08-20 16:12     ` David G. Johnston <[email protected]>
2021-08-21 10:54       ` Roger Mason <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox