From: Tom Lane <tgl@sss.pgh.pa.us>
To: Roger Mason <rmason@mun.ca>
cc: pgsql-novice@postgresql.org
Subject: Re: plpgsql select into
In-reply-to: <y65a6lco0z4.fsf@mun.ca>
References: <y65a6lco0z4.fsf@mun.ca>
Comments: In-reply-to Roger Mason <rmason@mun.ca>
	message dated "Fri, 20 Aug 2021 08:08:07 -0230"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1853890.1629468768.1@sss.pgh.pa.us>
Date: Fri, 20 Aug 2021 10:12:48 -0400
Message-ID: <1853891.1629468768@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1853891.1629468768%40sss.pgh.pa.us>
Precedence: bulk

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