Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL Errors...

From: "Iain" <iain(at)mst(dot)co(dot)jp>
To: "Chari Clark" <cclark(at)nationalbackgrounddata(dot)com>,<pgsql-admin(at)postgresql(dot)org>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: PostgreSQL Errors...
Date: 2005-02-21 06:42:23
Message-ID: 000c01c517e0$830679c0$7201a8c0@mst1x5r347kymb (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-novice
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

In response to

pgsql-novice by date

Next:From: Stefan.ArdeleanuDate: 2005-02-21 10:31:12
Subject: Help in dynamic execution in plpgsql
Previous:From: Michael FuhrDate: 2005-02-21 04:22:36
Subject: Re: Concatenate rows

pgsql-admin by date

Next:From: dzhaoDate: 2005-02-21 12:12:56
Subject: GRASS-install
Previous:From: John DeSoiDate: 2005-02-21 06:27:59
Subject: Re: Access Permissions to Users

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group