2

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?

Andrew Bystrov
  • 123
  • 1
  • 6
  • 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. – Erwin Brandstetter Dec 19 '18 at 14:56

1 Answers1

6

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);
  • decorating the function with stable may prevent inlining in some circumstances, reducing possible optimisation. – Jasen Dec 19 '18 at 08:10
  • @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" –  Dec 19 '18 at 08:18
  • I think LANGUAGE SQL functions are a special case, I'll go look at the documentation and report back. – Jasen Dec 19 '18 at 08:21
  • https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions : it seems that stable does not prevent inlining unless it is a lie – Jasen Dec 19 '18 at 08:30
  • 1
    @Jasen: Actually, the function can and should be IMMUTABLE since the body is all-immutable. See: https://dba.stackexchange.com/questions/212195/can-declaring-function-volatility-immutable-harm-performance – Erwin Brandstetter Dec 19 '18 at 14:48