CODEWITHSUNDEEP
sqlpostgresqlplpgsql
jamiet
jamiet

Reputation: 12264

Filter table in a function using a table valued parameter

I have a table-valued function that uses a parameter to filter a table and returns the results:

drop table if exists mytbl;
create table mytbl (i int);
insert into mytbl (i) values (1),(2),(3);

CREATE FUNCTION filterer(
    _filter int
)
RETURNS TABLE(i_ret int)
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RETURN  QUERY   SELECT  i 
                    FROM    mytbl
                    WHERE   i = _filter;
END;
$BODY$;
select * from filterer(1);

it returns:

enter image description here

So far so good. However what I really want to do is filter on a list of values rather than just one. I figured the way to do it is create a table-valued parameter using a type that I define using CREATE TYPE, but I can't get it working. Here's what I have so far:

drop table if exists mytbl;
create table mytbl (i int);
DO $$
BEGIN
    IF NOT EXISTS (SELECT   * FROM  pg_type t WHERE t.typname = 'tp') THEN
        CREATE TYPE tp AS (i int);
    END IF;
END$$;
insert into mytbl (i) values (1),(2),(3);
DROP FUNCTION public.filterer(tp);
CREATE FUNCTION filterer(
    _filter tp
)
RETURNS TABLE(i_ret int)
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RETURN  QUERY   SELECT  i 
                    FROM    mytbl
                    WHERE   i IN (_filter);
END;
$BODY$;

select * from filterer(1);

When I run that code it fails with:

ERROR: function filterer(integer) does not exist LINE 23: select * from filterer(1);

How do I declare a variable of type tp and pass it into my function?

Upvotes: 0

Views: 684

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51529

working example

Two things here:

 WHERE   i IN (_filter);

should be replaced with

 WHERE   row(i) IN (_filter);

and calling:

select * from filterer(row(1));

I assume the question is purely academic, as I can't see any use of such type?..

CREATE TYPE tp AS (i int);

Upvotes: 1

Related Questions