How do I pass a list of values in postgres function without using array

Question

I need to create a function where the input parameter is a list of values with a single column:

Column1
Abc
Xyz
Mno
    
    

Create or replace function dummy(my_list <list of 3 values>) returns refcursor as
    .......
    $$
       Open ref for select * from table1 where col1 in (my_list);

       Return ref
    End;

Can someone please let me know how I can do this without using an array?

Also please let me know how I can call it in pgAdmin.

EDIT : Below is the code I am trying:

CREATE TYPE public.row1 AS
    (col1 character varying);

CREATE OR REPLACE FUNCTION public.xyz(
    i_a character varying,
    i_b row1[] DEFAULT array[NULL]::row1[])
    RETURNS refcursor
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
ref refcursor='o_c'; -- Declare a cursor variable
BEGIN
    OPEN ref FOR
    select * from es_forecast_period_ngf a where a.fiscal_quarter_name=i_a and a.fiscal_period in any(i_b.col1);
RETURN ref; -- Return the cursor to the caller
END;
$BODY$;
    
    calling mechanism :


BEGIN;
    SELECT xyz('Q3 FY2031',array[row('Q3-M1'),row('Q3-M2')]::row1[]);
    FETCH ALL in "o_c";
    COMMIT;
    
    error while compiling:


ERROR:  syntax error at or near "any"
    LINE 13: ... a.fiscal_quarter_name=i_a and a.fiscal_period in any(i_b.co...
                                                                  ^
    SQL state: 42601
    Character: 375

We are currently migrating our entire oracle database to postgres. In oracle we have functions where we are returning or taking refcursor as input and applying some business logic to it. For this particular scenario, we have an API Mulesoft layer which directly fires the query to oracle like : select * fron table where col1 in #payload. Payload here is a list of values. We want to have everything in function so that API doesnt run any queries directly. Hence we want a function where the input will be payload which is a list of values and we will return a cursor to api with the results. — Goutam Sahoo
– Goutam Sahoo, Commented Feb 25, 2021 at 13:06

Laurenz Albe · Accepted Answer · 2021-02-26 02:30:58Z

5

You could use a variadic function (this example uses integer and is written in SQL, but that makes no difference):

CREATE FUNCTION dummy(VARIADIC args integer[]) RETURNS bigint
   LANGUAGE sql AS
'SELECT count(*)
FROM generate_series(1, 9) AS i
WHERE i = ANY (args)';

SELECT dummy(1, 5, 12);

 dummy 
-------
     2
(1 row)

SELECT dummy(1, 2, 5, 6, 9);

 dummy 
-------
     5
(1 row)

Such a function can be called with any number of arguments, but they all have to have the same type (unless you want to code C).

The values passed to such a variadic argument (which must be the last in the argument list) form an array inside the function body.

edited Feb 26, 2021 at 2:30

answered Feb 25, 2021 at 13:05

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Goutam Sahoo Over a year ago

So there is no there way of doing this without using an array? Can you please let me know how i will call this function with 2 inputs. 1st is a varchar with username and the 2nd will be an array as per your suggestion with multiple values. Query inside function will be: select * from table1 where col1=username and col2 in array_list; result will be returned in refcursor. Can you please tell me how will i call this function in PgAdmin with some sample inputs to test it. Also can you tell me if i can use a type for this? Does type only hold 1 record??

Goutam Sahoo Over a year ago

Also i want to see how i can do this using plpgsql

Goutam Sahoo Over a year ago

What if i want to pass an object will multiple rows and columns? How do i do that? Also is array the only way to do this?

Laurenz Albe Over a year ago

Sure. Since each data type automatically has a corresponding array type in PostgreSQL, that is no problem.

Goutam Sahoo Over a year ago

Hi Laurenz, can you see my latest edit and suggest

|

Stefanov.sm · Accepted Answer · 2021-02-26 10:00:08Z

-1

Another idea is to pass the list of values as text that can be cast to array. Details on array syntax here.
Assuming that the list consists of text values then:

create or replace function dummy(my_list text) returns refcursor as
........
open ref for select * from table1 where col1 = any(('{'||my_list||'}')::text[]);

Here you always pass the list of values as text.
If integer values then ('{'||my_list||'}')::text[] becomes ('{'||my_list||'}')::integer[] and so on for other data types. Please also note that the list of values text needs to be properly escaped.

edited Feb 26, 2021 at 10:00

answered Feb 26, 2021 at 9:33

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

9 Comments

Goutam Sahoo Over a year ago

Hi Stefavov.sm, can you see my latest edit and suggest

user330315 Over a year ago

('{'||my_list||'}')::text[] can be simplified to (string_to_array(my_list, ','))

Stefanov.sm Over a year ago

@GoutamSahoo change in any to = any

Stefanov.sm Over a year ago

@GoutamSahoo I guess that es_forecast_period_ngf.fiscal_period is of type text. Why shall you need i_b row1[] instead of i_b text[] and select xyz('Q3 FY2031', 'Q3-M1, Q3-M2') using my suggestion above?

Goutam Sahoo Over a year ago

This is a sample where we are using a type with 1 column. But eventually we will have 2d arrays(for example a type with col1 and col2) in which we would only want to check for 1 column (lets say col2)

|

Collectives™ on Stack Overflow

How do I pass a list of values in postgres function without using array

2 Answers 2

6 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related