Re: [ADMIN] PostgreSQL Errors...

[Iain]

Hi,
try doing it this way and work from there:
select sp_items('85000000000001');
regards
Iain
----- Original Message ----- 
From: "Chari Clark" <[EMAIL PROTECTED]>
To: <pgsql-admin@postgresql.org>; <[EMAIL PROTECTED]>
Sent: Friday, February 18, 2005 5:25 AM
Subject: [ADMIN] PostgreSQL Errors...


I created the following statement:
CREATE TYPE get_item_info_type AS (full_name varchar(300),dob 
varchar(300));

CREATE OR REPLACE FUNCTION sp_items(int8) RETURNS get_item_info_type AS
'
   DECLARE
       in_id      items.item_no%TYPE;
   BEGIN
       select
           coalesce(full_name, '') as fullname,
           coalesce(dob, '') as dob
       from
           items
       where
           item_no = in_id
       ;
   RETURN;
   END;
'
LANGUAGE 'plpgsql';
This statement compiles fine, but when calling the funtion from a SELECT 
statement for a specific item, I get the following error message:

select * FROM sp_items('85000000000001');
ERROR:  return type mismatch in function returning tuple at or near ";"
CONTEXT:  compile of PL/pgSQL function "sp_aliases" near line 14
In order to return fullname and dob, I'm not sure if the data type for 
sp_items needs to match the get_item_info_type or what.

I think this is my last hurdle, so any help would be over-greatly 
appreciated.

Thanks,
--
Chari Clark
Database Administrator
National Background Data, LLC
A First Advantage Company (NASDAQ:  FADV)
352.629.9913
[EMAIL PROTECTED]
www.nationalbackgrounddata.com
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org