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:

1. Can I create function my_function like above?

2. If point 1 is true, what type I need to write for parameter? And what I need write on where clause?

• Erwin Brandstetter: If the question would declare the Postgres version(s) it is for, I would upvote. (SELECT version() helps.) Because it's a good question otherwise.

Answer 1

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);

• Jasen: decorating the function with stable may prevent inlining in some circumstances, reducing possible optimisation.
• user1822: @Jasen: hmm, I thought this was a minimum for being able to be inlined. Would a volatile ever be inlined? The manual claims that: "For best optimization results, you should label your functions with the strictest volatility category that is valid for them"
• Jasen: I think LANGUAGE SQL functions are a special case, I'll go look at the documentation and report back.
• Jasen: wiki.postgresql.org/wiki/Inlining_of_SQL_functions : it seems that stable does not prevent inlining unless it is a lie
• Erwin Brandstetter: @Jasen: Actually, the function can and should be IMMUTABLE since the body is all-immutable. See: dba.stackexchange.com/questions/212195/…