PostgreSQL functions inside where clause

Question

I have table like this:

table a (
   id 
   --
   p1 text,
   p2 text,
   p3 text
)

When I write some function, which use this table, I need to write code like this

create or replace function ...

select * from a
   where a.p1 is not null
     and a.p2 is not null
     and a.p3 is not null

I want to create a function,

 create function my_function(??? val) returns boolean as -- what I need to place instead of ???
 $$
 begin
     return val.p1 is not null 
        and val.p2 is not null
        and val.p3 is not null;
 end;
 $$ language plpgsql

in which I will pass "this" instance of current row (I don't know, how it called) and my functions will be like this:

select * from a
   where my_function(???) -- what I need to place instead of ???

My questions are:

Can I create function my_function like above?
If point 1 is true, what type I need to write for parameter? And what I need write on where clause?

score 7 · Accepted Answer · answered Dec 19 '18 at 08:00

For every table that is created a type with the same name is created. So you you can define your function to accept the table's type.

Such a function is also better written as a SQL function to avoid the overhead of PL/pgSQL.

create function my_function(val a) returns boolean as 
 $$
 select val.p1 is not null 
    and val.p2 is not null
    and val.p3 is not null;
 $$ 
 language sql
 stable;

The a in (val a) is the name of your table.

You can use that function in the following way:

select * 
from a
where my_function(a);

PostgreSQL functions inside where clause

1 Answers1