plpgsql function returning SETOF

From: "Johannes Brgmann" <johannes(at)jottbee(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Subject: plpgsql function returning SETOF
Date: 2005-12-21 15:54:43
Message-ID: 5zirti30os.fsf@jottbee.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello all novices and experts,

I didn't understand how to create a function, which returns a varying
number of records. Worse, it seems, that I didn't understand the SETOF
feature at all... Two examples:

1.)

should give two records in the year 1990: 1990.06.17 and 1990.10.03,
all other years have minimum 1 NULL record (so at some later point I
can filter with WHERE or so; only for timezone = 'CET' and datestyle =
'German')

CREATE OR REPLACE FUNCTION tagderdeutscheneinheit(TIMESTAMP WITH TIME ZONE)
RETURNS SETOF TIMESTAMP WITH TIME ZONE
CALLED ON NULL INPUT STABLE AS $$
DECLARE
in_ts timestamp with time zone;
rest_ts varchar(14);
yyyy smallint;
BEGIN
IF $1 IS NULL
THEN in_ts := localtimestamp(0);
ELSE in_ts := $1;
END IF;

yyyy := to_number(date_part('year',in_ts),'9999');
rest_ts := to_char(date_part('year',in_ts),'9999') || to_char(date_part('hour',in_ts),'99')
|| to_char(date_part('minute',in_ts),'99') || to_char(date_part('seconds',in_ts),'99');

SELECT CASE WHEN yyyy > 1951 AND yyyy <= 1990 THEN to_timestamp('03.10.' || rest_ts,'DD MM YYYY HH24 MI SS')
ELSE NULL
END
UNION ALL
SELECT CASE WHEN yyyy >= 1990 THEN to_timestamp('17.06.' || rest_ts,'DD MM YYYY HH24 MI SS')
ELSE NULL
END;

END;
$$ LANGUAGE plpgsql;

what i get:
ERROR: SELECT query has no destination for result data

2.)

similar to the above function, the below have been declared; each of
them returns exactly one value of type timestamp with time zone.

CREATE TYPE feiertag AS (bezeichnung VARCHAR(100), datum TIMESTAMP WITH TIME ZONE);

CREATE OR REPLACE FUNCTION feiertage(TIMESTAMP WITH TIME ZONE)
RETURNS SETOF feiertag
CALLED ON NULL INPUT AS $$
DECLARE
in_ts timestamp with time zone;
BEGIN
IF $1 IS NULL
THEN in_ts := localtimestamp(0);
ELSE in_ts := $1;
END IF;

SELECT 'Allerheiligen ', allerheiligen(in_ts)
UNION SELECT 'Aschermittwoch ', aschermittwoch(in_ts)
...
UNION SELECT 'Tag der deutschen Einheit ', tagderdeutscheneinheit(in_ts)
UNION SELECT 'Zweiter Weihnachtstag ', zweiterweihnachtstag(in_ts) ;
END;
$$ LANGUAGE plpgsql;

a result "table" like this is what I want:

bezeichnung | datum
-----------------+-------
Allerheiligen | ....

what i get:

bruegmann(at)traffic_nrw=# select feiertage(NULL);
ERROR: SELECT query has no destination for result data
HINT: If you want to discard the results, use PERFORM instead.
CONTEXT: PL/pgSQL function "feiertage" line 9 at SQL statement

What I don't understand here is: this is a fixed number of records, a
composit type for setof has been declared, so what else is missing or
is wrong?

Thanks for any help,
Johannes

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Chandra Sekhar Surapaneni 2005-12-21 16:02:54 Re: can not create trigger with parameter
Previous Message Oleg 2005-12-21 15:49:38 Re: transfer database from Linux to Windows