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