Re: PostgreSQL Errors...

Hi,

try doing it this way and work from there:

select sp_items('85000000000001');

regards
Iain

----- Original Message -----
From: "Chari Clark" <cclark(at)nationalbackgrounddata(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>; <pgsql-novice(at)postgresql(dot)org>
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
> cclark(at)nationalbackgrounddata(dot)com
> www.nationalbackgrounddata.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster