public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Roger Mason <[email protected]>
Cc: [email protected]
Subject: Re: plpgsql select into
Date: Fri, 20 Aug 2021 10:12:48 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: plpgsql select into
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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