Re: select into composite type / return

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема Re: select into composite type / return
Дата
Msg-id CAFj8pRDaD1L85UxP6ScQv4GUnEqMw9EjpFmTqippRZL1DGhT5Q@mail.gmail.com
обсуждение исходный текст
Ответ на Re: select into composite type / return  (Gary Stainburn <gary.stainburn@ringways.co.uk>)
Ответы Re: select into composite type / return  (Gary Stainburn <gary.stainburn@ringways.co.uk>)
Список pgsql-sql
Hi

po 22. 3. 2021 v 11:34 odesílatel Gary Stainburn <gary.stainburn@ringways.co.uk> napsal:
I've added another function, partly to aid debugging, partly to test the
next part of the project.

The idea is simple.  select the results of the calculation into a local
variable and then process it.  However, I can't get the select to work. 
The failure message relates to the "select into D" line.

gary=# select * from read_breakdown(1);
ERROR:  invalid input syntax for type numeric:
"(1.00,2.00,3.00,4.00,5.00,6.00)"
CONTEXT:  PL/pgSQL function read_breakdown(integer) line 12 at SQL statement
gary=#


create or replace function read_breakdown(vID int4)  RETURNS breakdown
AS $$
DECLARE
   v RECORD;
   D breakdown;
BEGIN
   IF vID IS NULL THEN RETURN NULL; END IF;
   select into v * from sessions s where s.ID = vID;
   IF NOT FOUND THEN
     RAISE NOTICE 'breakdown: % not found',vID;
     RETURN NULL;
   END IF;
   select into D do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7);
   IF NOT FOUND THEN
     RAISE NOTICE 'breakdown: % calculation failed',vID;
     RETURN NULL;
   END IF;
   RAISE NOTICE 'read_breakdown: f1=%',D.f1;
   RAISE NOTICE 'read_breakdown: f2=%',D.f2;
   RAISE NOTICE 'read_breakdown: f3=%',D.f3;
   RAISE NOTICE 'read_breakdown: f4=%',D.f4;
   RAISE NOTICE 'read_breakdown: f5=%',D.f5;
   RAISE NOTICE 'read_breakdown: f6=%',D.f6;
   RETURN D;
END;
$$
LANGUAGE PLPGSQL;


  select into D do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7);

this line is wrong

you should to use select * from do_breakdown(..) into D

note - plpgsql is case insensitive language - all identifiers are transformed to lower case form - so using identifiers like D is not a good idea.

Regards

Pavel

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

Предыдущее
От: Gary Stainburn
Дата:
Сообщение: Re: select into composite type / return
Следующее
От: Gary Stainburn
Дата:
Сообщение: Re: select into composite type / return