Re: select into composite type / return

Hi

po 22. 3. 2021 v 11:34 odesílatel Gary Stainburn <
gary(dot)stainburn(at)ringways(dot)co(dot)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