Thread: Returning A Varchar From A Function

Hi 

this has been puzzling me for a few days now

I have created a function that I want to use in a database to select a
value from a table based on the value passed in.
The table is fairly simple
CREATE TABLE public.feeder_next_status
( status varchar NOT NULL, previous_status varchar, next_status varchar
) WITH OIDS;

The function is defined as 

-- Function: public.spgetnextstatus(varchar)

-- DROP FUNCTION public.spgetnextstatus(varchar);

CREATE OR REPLACE FUNCTION public.spgetnextstatus(varchar)
RETURNS varchar AS
'
select cast(next_status as varchar) 
from feeder_next_status 
where trim(status) = trim(\'$1\')
order by next_status;'
LANGUAGE 'sql' STABLE;


However when i run the query 
select spgetnextstatus('NEW')
in pgadmin
the dataoutput shows two columns
the row column shows a row number of '1' and the column header
spgetnextstatus(varchar) shows blank

I have tested the equivalent sql in the pgadmin query and it works
fine.

Hoping someone can point me inthe right direction

Hi Richard,

What happens if you just do:

where trim(status) = trim($1)

Regards,
George

----- Original Message ----- 
From: "Richard Hurst" <richard.hurst@kirklees.gov.uk>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, August 12, 2004 4:04 AM
Subject: [SQL] Returning A Varchar From A Function


> Hi 
> 
> this has been puzzling me for a few days now
> 
> I have created a function that I want to use in a database to select a
> value from a table based on the value passed in.
> The table is fairly simple
> CREATE TABLE public.feeder_next_status
> (
>   status varchar NOT NULL,
>   previous_status varchar,
>   next_status varchar
> ) WITH OIDS;
> 
> The function is defined as 
> 
> -- Function: public.spgetnextstatus(varchar)
> 
> -- DROP FUNCTION public.spgetnextstatus(varchar);
> 
> CREATE OR REPLACE FUNCTION public.spgetnextstatus(varchar)
> RETURNS varchar AS
> '
> select cast(next_status as varchar) 
> from feeder_next_status 
> where trim(status) = trim(\'$1\')
> order by next_status;'
> LANGUAGE 'sql' STABLE;
> 
> 
> However when i run the query 
> select spgetnextstatus('NEW')
> in pgadmin
> the dataoutput shows two columns
> the row column shows a row number of '1' and the column header
> spgetnextstatus(varchar) shows blank
> 
> I have tested the equivalent sql in the pgadmin query and it works
> fine.
> 
> Hoping someone can point me inthe right direction
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>