Execute Function First in Postgres

Question

I need to call a FUNCTION in postgreSQL before a SELECT is done on a table. My first thought was to use a TRIGGER but it appears you cannot trigger on select.

Thus, to work around this I created a VIEW that runs a select on the table and the function all at once. I.e.:

CREATE VIEW people_view AS
    SELECT get_department(), name, title, department
    FROM people_table

So, in a nutshell... the get_department() function would update the department column from external data (this is all using foreign data tables and wrappers).

The problem is, the function executes after name, title, department are selected and not before. So if I run it once it doesn't work. If I run it twice it does (because it updated after the first run).

Sorry if this doesn't make much sense. I don't typically do DB work. What I'd like to do is get "get_department()" to execute first in the SELECT. I tried to put the function call in a sub-query, but it still did not execute first. The only thought I have left is perhaps an explicit join to force the order? I have no idea :-(.

Basically, I just want to execute a function before a SELECT transparently for the person running the query... and I guess you can't do that with triggers. If there's a better workaround, I'd be more than happy to give it a go.

Thanks, Isekal

Abelisto · Accepted Answer · 2016-02-27 18:18:28Z

2

with t(x) as (
  select get_department()) -- Function executes here
select
  t.x, ...
from
  t, people_table -- You does not provide any relation between your function and table...

Also check LATERAL feature.

answered Feb 27, 2016 at 18:18

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5990763 Over a year ago

This worked perfectly! I never knew WITH existed (I don't do a lot of DB work these days). Unfortunately, I'm on 9.2 so LATERAL wasn't an option (though I probably can, and will upgrade).

Daniel Faure · Accepted Answer · 2016-02-27 18:11:06Z

May be you construct a function that returns your table, and includes call to your function before selecting data.

CREATE OR REPLACE FUNCTION people_table()
  RETURNS TABLE(name character varying, title character varying, department character varying) AS
$BODY$
    BEGIN

  -- do function call
  SELECT get_department();

  RETURN QUERY 
    SELECT people_table.* FROM people_table;    

  END;
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

-- then, later in your code, use table selecting on the new function
  SELECT * from people_table();

-- notice the use of parenthesis.

-- you may also 
  SELECT name FROM people_table() ORDER BY department DESC;
-- and use the function as if it were the table itself.

Hope it helps.

Collectives™ on Stack Overflow

Execute Function First in Postgres

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related