Returning a varchar from a functions

Started by Richard Hurstover 21 years ago2 messagesgeneral
Jump to latest
#1Richard Hurst
richard.hurst@kirklees.gov.uk

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
REATE 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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Richard Hurst (#1)
Re: Returning a varchar from a functions

"Richard Hurst" <richard.hurst@kirklees.gov.uk> writes:

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;

Try it without the quotes:
where trim(status) = trim($1)
As is, the function is probably returning NULL because there are
no rows matching the literal value "$1".

regards, tom lane