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.

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.klaver@aklaver.com

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 <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>, 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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com