CODEWITHSUNDEEP
postgresqlplpgsqlstring-aggregation
Louis Royster
Louis Royster

Reputation: 121

Using variables in a plpgsql function

Ok so I used a string_agg like this. 

select string_agg(DISTINCT first_name,', ' ORDER BY first_name) FROM person_test;

Then I wrote this to return the values to a table. 

SELECT *
FROM person_test
where first_name = ANY(string_to_array('Aaron,Anne', ','));

Now I want to put this in a function so that instead of acturally putting names into the string_to_array, I can just call the string_agg.

I am new to postgres and am not finding any good documentation on how to do this online. I believe I would have to declare the the string_agg and then call it in string_to_array but I am having no such luck.

This was my attempt, I know this is now right but if anyone could add some feedback. I am getting an error between results and ALAIS and on the return. 

create or REPLACE FUNCTION select_persons(VARIADIC names TEXT[]);
declare results ALIAS select string_agg(DISTINCT first_name,', ' ORDER BY first_name) FROM person_test;
BEGIN
return setof person_test LANGUAGE sql as $$
  select * from person_test
  where first_name = any(results)
end;
$$ language sql;

Upvotes: 0

Views: 867

Answers (1)

klin
klin

Reputation: 121604

You can create a function with variable number of arguments.

Example:

create table person_test (id int, first_name text);
insert into person_test values
(1, 'Ann'), (2, 'Bob'), (3, 'Ben');

create or replace function select_persons(variadic names text[])
returns setof person_test language sql as $$
    select *
    from person_test
    where first_name = any(names)
$$;

select * from select_persons('Ann');
 id | first_name 
----+------------
  1 | Ann
(1 row)

select * from select_persons('Ann', 'Ben', 'Bob');
 id | first_name 
----+------------
  1 | Ann
  2 | Bob
  3 | Ben
(3 rows)

To use a variable inside a plpgsql function, you should declare the variable and use select ... into (or assignment statement). Example:

create or replace function my_func()
returns setof person_test
language plpgsql as $$
declare
    aggregated_names text;
begin
    select string_agg(distinct first_name,', ' order by first_name) 
    into aggregated_names
    from person_test;

    -- here you can do something using aggregated_names

    return query 
        select *
        from person_test
        where first_name = any(string_to_array(aggregated_names, ', '));
end $$;

select * from my_func();

Upvotes: 1

Related Questions