INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION

İlyas Derse
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 ? 
Reply | Threaded
Open this post in threaded view
|

Re: INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION

Thomas Kellerer
İ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.



Reply | Threaded
Open this post in threaded view
|

Re: INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION

Adrian Klaver-4
In reply to this post by İlyas Derse
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
[hidden email]