Message-ID: <6af818d092cf9afb1f7228bca0cbab03ae17bf4d.camel@cybertec.at>
Subject: Re: plpgsql select into
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Roger Mason <rmason@mun.ca>, pgsql-novice@postgresql.org
Date: Fri, 20 Aug 2021 16:13:47 +0200
In-Reply-To: <y65a6lco0z4.fsf@mun.ca>
References: <y65a6lco0z4.fsf@mun.ca>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/6af818d092cf9afb1f7228bca0cbab03ae17bf4d.camel%40cybertec.at>
Precedence: bulk

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