plpgsql select into

Поиск
Список
Период
Сортировка
От Roger Mason
Тема plpgsql select into
Дата
Msg-id y65a6lco0z4.fsf@mun.ca
обсуждение исходный текст
Ответы Re: plpgsql select into  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: plpgsql select into  (Laurenz Albe <laurenz.albe@cybertec.at>)
Список pgsql-novice
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



В списке pgsql-novice по дате отправления:

Предыдущее
От: "Nicolas Mitchell"
Дата:
Сообщение: Re: Trigger function
Следующее
От: Tom Lane
Дата:
Сообщение: Re: plpgsql select into