Re: INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION
On 11/13/19 11:58 PM, İlyas Derse wrote:
Please reply to list also.
Ccing list.
I want to do like this ;
I can't make sense of the below. I don't see where "x" and "y" are used
in the function, unless they supposed to be "id" and "filesize". I have
no idea what the QUERY is doing? Pretty sure you don't want an
unconstrained select on a table. Can you provide a working example of
what you are doing in SQL Server with sample output from same?
|CREATEorREPLACE FUNCTIONpublic."test"(INOUT "x"integer,INOUT
"y"text)RETURNS TABLE("id"integer,"filesize"character
varying(36))AS$$BEGINRETURNQUERY
SELECT*FROMpublic."tbl_employees";END;$$LANGUAGE plpgsql;|
|I need to call table and inout parameters together at another place.|
Adrian Klaver >, 8 Kas 2019 Cum, 18:25 tarihinde
şunu yazdı:
On 11/8/19 12:18 AM, İlyas Derse wrote:
> 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.
Can you show an example of a SQL Server procedure that demonstrates
what
you want to achieve?
>
> Its like ;
>
> 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 ?
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION
On 11/8/19 12:18 AM, İlyas Derse wrote: 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. Can you show an example of a SQL Server procedure that demonstrates what you want to achieve? Its like ; 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 ? -- Adrian Klaver adrian.kla...@aklaver.com
Re: INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION
İ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.
INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION
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. Its like ; 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 * FROMpublic."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 ?
