From: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION |
Date: | 2019-11-08 12:40:27 |
Message-ID: | 0dd3d441-42f8-daf6-7549-2bde894a3af1@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
İlyas Derse schrieb am 08.11.2019 um 09:18:
> I'm trying to migration to PostgreSql from SQL Server. I have Stored Procedures what have output parameters and return tables. How can i do both together.
>
> CREATE or replace FUNCTION public."test" (INOUT "x" integer, INOUT "y" character varying(36))
> RETURNS TABLE (
> "id" integer,
> "filesize" character varying(36)
> )
> AS $$
> BEGIN
> x=6;
> RETURN QUERY
> SELECT * FROM public."tbl_employees" ;
>
> END;
> $$ LANGUAGE plpgsql;
>
> I can not create that because of inout parameters.
> Another place;
>
> do $$
> DECLARE b integer = 1;
> DECLARE d integer = 2 ;
> BEGIN
> select * from public."test"();
> END;
> $$;
>
> Anybody have an idea ?
Can't you just include the "out" parameters in the result?
CREATE or replace FUNCTION public.test(x integer, y character varying(36))
RETURNS TABLE (id integer, filesize character varying(36), x integer, y varchar)
AS $$
begin
x := 42;
y := 'foo';
RETURN QUERY
SELECT t.*, x, y
FROM public.tbl_employees t;
END;
It's different because x and y are repeated for every row, but that's the only thing I can think of.
From | Date | Subject | |
---|---|---|---|
Next Message | Zwettler Markus (OIZ) | 2019-11-08 12:51:33 | AW: AW: AW: AW: broken backup trail in case of quickly patroni switchback and forth |
Previous Message | Zwettler Markus (OIZ) | 2019-11-08 11:48:46 | AW: broken backup trail in case of quickly patroni switchback and forth |