Re: select into composite type / return

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Re: select into composite type / return
Date: 2021-03-22 10:34:13
Message-ID: 9fb9f646-58be-cb17-b144-664c46f6cd01@ringways.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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;

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Pavel Stehule 2021-03-22 10:40:08 Re: select into composite type / return
Previous Message Gary Stainburn 2021-03-18 14:32:50 Re: select into composite type / return