Re: plpgsql select into

Roger Mason <rmason(at)mun(dot)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