From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@lists.postgresql.org
Subject: Re: select into composite type / return
Date: Mon, 22 Mar 2021 10:34:13 +0000
Message-ID: <9fb9f646-58be-cb17-b144-664c46f6cd01@ringways.co.uk> (raw)
In-Reply-To: <e13395d5-10e5-3e83-1dd1-ad5d98d0e8b7@ringways.co.uk>
References: <39dcfab9-4eb2-382b-9186-baf722ca56a0@ringways.co.uk>
<3523853.1616002010@sss.pgh.pa.us>
<9eb42b06-9cfa-1e4b-e42b-6f64e99675a5@ringways.co.uk>
<7af5d693-a6a3-d3d4-050a-0898aad8bd77@ringways.co.uk>
<CAGqkgphmj3uHB3Jc5Eb=yA2cfffHFsACWR3Fbfr6jrkQepKJPA@mail.gmail.com>
<3718235.1616077713@sss.pgh.pa.us>
<e13395d5-10e5-3e83-1dd1-ad5d98d0e8b7@ringways.co.uk>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk, pgsql-sql@lists.postgresql.org
Subject: Re: select into composite type / return
In-Reply-To: <9fb9f646-58be-cb17-b144-664c46f6cd01@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox