IronPort-SDR: 
 Mj8p0kNv3R0pCn33Dt7+zAuvwnHER5KoZrihmGlnxGPbfOUqDnJbGLRttwLWSdYIoA8m6+iWjv
 uD3tCbpynU8X1CfMzI7nQUSiAsCLSaHXII9a2jzTtyrHT+RIlKPTZxNadGUoLELxdXMiI1OamZ
 IJrejtR4KR1dqI1Yosmk9E5Sn69TMNdrneuBMpDC5pbtam5yNoIqGnnjUhR6RunjJddzPQesYo
 FcAO7IwwEY+lMfgsTk0adw6E8Mt2ExhOY8ZbxnkHOlBSX30IPWk4kzBCV1IDEwPavycxThFoGy
 4lM=
IronPort-HdrOrdr: =?us-ascii?q?A9a23=3AEdHEHqmxf9uVm9o3M2QWcYNBzzjpDfIZ3D?=
 =?us-ascii?q?Abv31ZSRFFG/Fwz/re+cjzpiWE7Ar5OUtQ4exoV5PgfZqxz/RICMwqTNWftW?=
 =?us-ascii?q?rdyRCVxeNZjbcKqgeIc0bDH6xmpMRdmsNFZOEYeGIVsS+M2maF+rgbreVvu5?=
 =?us-ascii?q?rY4ts2h00dKz2CRZsQljtENg=3D=3D?=
User-agent: mu4e 1.5.6; emacs 27.2
From: Roger Mason <rmason@mun.ca>
To: pgsql-novice@postgresql.org 
Subject: plpgsql select into
Date: Fri, 20 Aug 2021 08:08:07 -0230
Message-ID: <y65a6lco0z4.fsf@mun.ca>
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: <https://www.postgresql.org/message-id/y65a6lco0z4.fsf%40mun.ca>
Precedence: bulk

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