Postgres Pro
Downloads
Home   >   mailing lists

Thread: INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION

INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION

From
İlyas Derse
Date:
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 * 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 ? 

Re: INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION

From
Thomas Kellerer
Date:
İ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
returntables. 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.

Re: INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION

From
Adrian Klaver
Date:
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

Re: INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION

From
Adrian Klaver
Date:
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