Re: SELECT INTO array[i] with PL/pgSQL

Поиск
Список
Период
Сортировка
От Dmitriy Igrishin
Тема Re: SELECT INTO array[i] with PL/pgSQL
Дата
Msg-id AANLkTikqaEijqgw0h4Gp+4sTSCn+-6xP0wYmNAYTKKcC@mail.gmail.com
обсуждение исходный текст
Ответ на Re: SELECT INTO array[i] with PL/pgSQL  (Edoardo Panfili <edoardo@aspix.it>)
Список pgsql-general
Дерево обсуждения
Hey,

2011/2/8 Edoardo Panfili <edoardo@aspix.it>
On 07/02/11 22.15, Julia Jacobson wrote:
Dear PostgreSQL community,

Please consider the following minimal example:

CREATE TABLE example (row_id SERIAL, value TEXT);
INSERT INTO example(value) VALUES ('val1');
INSERT INTO example(value) VALUES ('val2');
INSERT INTO example(value) VALUES ('val3');

CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
a TEXT;
b TEXT[];
i INT;
BEGIN
FOR i in 1..3 LOOP
SELECT INTO a value FROM example WHERE row_id=i; -- This works
b[i] := a; -- perfectly!
-- SELECT INTO b[i] value FROM example WHERE row_id=i; Doesn't work!
END LOOP;
RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';

this one seems work...


CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
 b TEXT[];
 i INT;
BEGIN
 FOR i in 1..3 LOOP
   b[i]:= value FROM example WHERE row_id=i;
 END LOOP;
   RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';


Edoardo


CREATE OR REPLACE FUNCTION public.f1()
 RETURNS void
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
  a_ text[];
  t_ text; -- just for example of usage
BEGIN
  SELECT INTO a_ array_agg(dat) FROM t1;

  -- usage example:
  FOR t_ IN SELECT unnest(a_) LOOP
    RAISE NOTICE '%', t_;
  END LOOP;
END;
$function$

create table t1 (id serial, dat text);
insert into t1 (dat) select 'dima';
insert into t1 (dat) select 'alex';
insert into t1 (dat) select 'vasya';

dmitigr=> select f1();
NOTICE:  dima
NOTICE:  alex
NOTICE:  vasya

--
// Dmitriy.


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