pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL Errors...
2+ messages / 2 participants
[nested] [flat]
* PostgreSQL Errors...
@ 2005-02-17 20:25 Chari Clark <cclark@nationalbackgrounddata.com>
2005-02-21 06:42 ` Re: PostgreSQL Errors... Iain <iain@mst.co.jp>
0 siblings, 1 reply; 2+ messages in thread
From: Chari Clark @ 2005-02-17 20:25 UTC (permalink / raw)
To: pgsql-admin; pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: PostgreSQL Errors...
2005-02-17 20:25 PostgreSQL Errors... Chari Clark <cclark@nationalbackgrounddata.com>
@ 2005-02-21 06:42 ` Iain <iain@mst.co.jp>
0 siblings, 0 replies; 2+ messages in thread
From: Iain @ 2005-02-21 06:42 UTC (permalink / raw)
To: Chari Clark <cclark@nationalbackgrounddata.com>; pgsql-admin; pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-02-21 06:42 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-17 20:25 PostgreSQL Errors... Chari Clark <cclark@nationalbackgrounddata.com>
2005-02-21 06:42 ` Iain <iain@mst.co.jp>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox