Re: PostgreSQL equivalent to Oracles ANYDATASET

Hi,

many thanks for this suggestion. But the problem with this is that you have to know which columns are returned when you call the function.

Regards
Dirk

--
Dirk Mika
Software Developer

[cid:mt_c1c59b3d-dd43-4ca6-992b-79f5a19f5999.png]

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk(dot)mika(at)mikatiming(dot)de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika
Von: Christoph Moench-Tegeder <cmt(at)burggraben(dot)net>
Datum: Freitag, 13. November 2020 um 18:23
An: Dirk Mika <Dirk(dot)Mika(at)mikatiming(dot)de>
Cc: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Betreff: Re: PostgreSQL equivalent to Oracles ANYDATASET

## Dirk Mika (Dirk(dot)Mika(at)mikatiming(dot)de<mailto:Dirk(dot)Mika(at)mikatiming(dot)de>):

SELECT * FROM TABLE(series_pkg.get_results(1));
The purpose of this function is to provide a DATASET, which has
different columns in the result depending on the passed parameter.
Is there any way to achieve something similar in PostreSQL?

testing=# CREATE OR REPLACE FUNCTION public.rr(p INTEGER)
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $function$
BEGIN
IF p = 1 THEN
RETURN NEXT ('k1'::TEXT, 'v1'::TEXT);
RETURN NEXT ('k2'::TEXT, 'v2'::TEXT);
ELSE
RETURN NEXT (23::INTEGER, 42::INTEGER, 'abc'::TEXT);
RETURN NEXT (42::INTEGER, 23::INTEGER, 'xyz'::TEXT);
END IF;
RETURN;
END;
$function$;
CREATE FUNCTION

testing=# SELECT * FROM rr(2) f(a INTEGER, b INTEGER, c TEXT);
a | b | c
----+----+-----
23 | 42 | abc
42 | 23 | xyz
(2 rows)

testing=# SELECT * FROM rr(1) f(x TEXT, y TEXT);
x | y
----+----
k1 | v1
k2 | v2
(2 rows)

Regards,
Christoph

--
Spare Space