Thread: function problems
I am trying to write a postgres function and I'm getting errors when
the parameter sub is empty (it wont ever be null) and meetCode has a number.
This is the function code:
create or replace function SubjectDisplay(IN subj varchar,IN meetCode
numeric,IN meetTrWP integer,OUT Display varchar)
returns varchar as
$BODY$
Declare
meetType varchar;
BEGIN
IF subj='' Then
if meetCode=0 Then
if meetTrWp=0 Then
Select S_MEETING_TITLE as Display from System Limit 1;
Return;
elseif meetTrWp=1 Then
Select S_TRAINING_TITLE as Display from System Limit 1;
Return;
else
Select S_WP_TITLE as Display from System Limit 1;
Return;
end if;
else
--This is where I run into problems. I get an error when I run it.
Query has no destination for result data
Select MT_DESCRIPTION as meetType from MEETING_TYPE Where MT_CODE=meetCode;
Display := meetType
Return;
end if;
else
Display := subj
Return;
end if;
Return;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 100;
Christine Penner
On 16/11/2010 21:27, Christine Penner wrote: > create or replace function SubjectDisplay(IN subj varchar,IN meetCode > numeric,IN meetTrWP integer,OUT Display varchar) > > returns varchar as I'm not certain about this, but is it a mistake to mix OUT parameters and RETURNS? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
I have seen other functions (written by others) that do this. So I assume its ok. I'm open to suggestions though. As long as it works. At 01:54 PM 16/11/2010, you wrote: >On 16/11/2010 21:27, Christine Penner wrote: > >>create or replace function SubjectDisplay(IN subj varchar,IN meetCode >>numeric,IN meetTrWP integer,OUT Display varchar) >> >>returns varchar as > >I'm not certain about this, but is it a mistake to mix OUT >parameters and RETURNS? > >Ray. > > >-- >Raymond O'Donnell :: Galway :: Ireland >rod@iol.ie > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general
On 11/16/2010 3:57 PM, Christine Penner wrote: > I have seen other functions (written by others) that do this. So I > assume its ok. I'm open to suggestions though. As long as it works. > > At 01:54 PM 16/11/2010, you wrote: >> On 16/11/2010 21:27, Christine Penner wrote: >> >>> create or replace function SubjectDisplay(IN subj varchar,IN meetCode >>> numeric,IN meetTrWP integer,OUT Display varchar) >>> >>> returns varchar as >> >> I'm not certain about this, but is it a mistake to mix OUT parameters >> and RETURNS? >> >> Ray. >> >> >> -- >> Raymond O'Donnell :: Galway :: Ireland >> rod@iol.ie >> I have used OUT combined with "returns setof record", it makes the result set have the columns specified as OUT params. like: create or replace function TotalCustProd( xsdate timestamp, xedate timestamp, out xcust varchar(100), out xcontract varchar(80), out xjob text, out xtask text, out xparcels integer, out xhours float, out xrate float, out xpclperhour varchar(1), out xamount float ) RETURNS SETOF record AS $$ the result set would be xcust, xcontract, etc... Newer versions of PG have "returns table" support: create or replace function findBadRates(sdate date) returns table(rrowid integer, rlookuprate decimal(5,2)) as $$ Do you want to return one single value, or a set of rows? -Andy
On 16/11/2010 21:57, Christine Penner wrote: > I have seen other functions (written by others) that do this. So I > assume its ok. I'm open to suggestions though. As long as it works. OK, fair enough. What error are you getting? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
I am returning one value (text). Each of the selects in the function should also return only one value. At 02:06 PM 16/11/2010, you wrote: >On 11/16/2010 3:57 PM, Christine Penner wrote: >>I have seen other functions (written by others) that do this. So I >>assume its ok. I'm open to suggestions though. As long as it works. >> >>At 01:54 PM 16/11/2010, you wrote: >>>On 16/11/2010 21:27, Christine Penner wrote: >>> >>>>create or replace function SubjectDisplay(IN subj varchar,IN meetCode >>>>numeric,IN meetTrWP integer,OUT Display varchar) >>>> >>>>returns varchar as >>> >>>I'm not certain about this, but is it a mistake to mix OUT parameters >>>and RETURNS? >>> >>>Ray. >>> >>> >>>-- >>>Raymond O'Donnell :: Galway :: Ireland >>>rod@iol.ie > > >I have used OUT combined with "returns setof record", it makes the >result set have the columns specified as OUT params. > >like: >create or replace function TotalCustProd( xsdate timestamp, xedate timestamp, > out xcust varchar(100), out xcontract varchar(80), out xjob text, > out xtask text, out xparcels integer, > out xhours float, out xrate float, out xpclperhour varchar(1), > out xamount float >) RETURNS SETOF record AS $$ > > >the result set would be xcust, xcontract, etc... > >Newer versions of PG have "returns table" support: > >create or replace function findBadRates(sdate date) returns >table(rrowid integer, rlookuprate decimal(5,2)) as $$ > > >Do you want to return one single value, or a set of rows? > > >-Andy > > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general
On 16/11/2010 21:27, Christine Penner wrote:
> Select S_TRAINING_TITLE as Display from System Limit 1;
Looking again at your function, I think this (and other similar lines)
should be
select s_training_title into display ....
- i.e. "into" instead of "as".
HTH,
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function "subjectdisplay" line 7 at SQL statement ********** Error ********** ERROR: query has no destination for result data SQL state: 42601 Hint: If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function "subjectdisplay" line 7 at SQL statement At 02:09 PM 16/11/2010, Raymond O'Donnell wrote: >On 16/11/2010 21:57, Christine Penner wrote: >>I have seen other functions (written by others) that do this. So I >>assume its ok. I'm open to suggestions though. As long as it works. > >OK, fair enough. What error are you getting? > >Ray. > >-- >Raymond O'Donnell :: Galway :: Ireland >rod@iol.ie