Subject: Re: select into composite type / return
To: pgsql-sql@lists.postgresql.org
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>
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Message-ID: <9fb9f646-58be-cb17-b144-664c46f6cd01@ringways.co.uk>
Date: Mon, 22 Mar 2021 10:34:13 +0000
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <e13395d5-10e5-3e83-1dd1-ad5d98d0e8b7@ringways.co.uk>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
 
 Content preview:  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. [...] 
 
 Content analysis details:   (-48.8 points, 15.0 required)
 
  pts rule name              description
 ---- ---------------------- --------------------------------------------------
  -50 ALL_TRUSTED            Passed through trusted hosts only via SMTP
 -1.9 BAYES_00               BODY: Bayes spam probability is 0 to 1%
                             [score: 0.0000]
  2.0 FLOWED                 Conten Type format=flowed appears in the new SPAMS
  0.1 SCORE_RCPTS            Adding score for each recipient
 -1.0 AWL                    AWL: Adjusted score from AWL reputation of From: address
  1.0 MISSING_FROM           Missing From: header
 -0.0 NICE_REPLY_A           Looks like a legit reply (A)
  1.0 RING_SAFE              No description available.
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Archived-At: 
 <https://www.postgresql.org/message-id/9fb9f646-58be-cb17-b144-664c46f6cd01%40ringways.co.uk>
Precedence: bulk

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;