How to pass a record/row to a function

user_12375196 0
Viewed 136

I'm trying to understand how to pass a row/record as argument of a function in postgresql and I found this already existing question How to pass a table or rows to a function in Postgresql? where the selected answer explains really well how to pass a table as argument by using the array_agg() function.

In that case the argument of the function is an array of the type of the table and each element of the array matches a row and and each field of an element of the array, which can be read using the . notation, matches the columns of a row.

What's not explained in the answer is how to pass only one record/row as argument and my thought on that would be to pass a variable of tablename%ROWTYPE type and then use the . notation to access each field of the record like this: variable.field1, variable.field2 and so on.

Is this correct? If it is not, how can I pass a record which has the type of a table row as an argument to a function?

plpgsql postgresql
1 Answers

The function should accept a parameter of the same type as your table. Here is an example that returns a boolean, which you can use to filter the table (similar to a where clause, but this is just an example)

CREATE FUNCTION isvisible(p_row myTable)  RETURNS BOOLEAN
AS $BODY$
  select p_row.myCol = 'whatever';
$BODY$ 
LANGUAGE sql IMMUTABLE;

select * from myTable  t where t.isvisible;
select * from myTable  t where isvisible(t);
select * from myTable  t where isvisible(t.*);
user_76355690
Related