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?

1
  • 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. Dec 19, 2018 at 14:56

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.