Thread: 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@nationalbackgrounddata.com
www.nationalbackgrounddata.com

Hi,

try doing it this way and work from there:

select sp_items('85000000000001');

regards
Iain

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