CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsqlpostgresql-9.1
Borys
Borys

Reputation: 3034

How to get a statement calling the function from inside the function itself?

Let's say I have a function show_files(IN file text, IN suffix text, OUT statement text). In next step the function is called:

 SELECT * FROM show_files(file := 'example', suffix := '.png');

My question is: Is there any solution that I could get statement that has called this function from inside that function?

I mean, after running the SELECT the output of function (OUT statement text) should be: 'SELECT * FROM show_files(file := 'example', suffix := '.png');', or is it possible to assign this statement to the variable inside the function?

I need the functionality like those with TG_NAME, TG_OP, etc. in trigger procedures.

Maybe is it possible to retrieve this statement from SELECT current_query FROM pg_stat_activity ?

When I'm trying to use it inside a function I've got an empty record:

CREATE OR REPLACE FUNCTION f_snitch(text)
  RETURNS text AS
$BODY$
declare
rr text;
BEGIN
    RAISE NOTICE '.. from f_snitch.';
    -- do stuff
    SELECT current_query  into rr FROM pg_stat_activity 
    WHERE current_query ilike 'f_snitch';
    RETURN rr;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Any help and suggestions would be happily welcome!

Upvotes: 1

Views: 1737

Answers (2)

Borys
Borys

Reputation: 3034

Ok, I've got it!

CREATE OR REPLACE FUNCTION f_snitch(text)
  RETURNS setof record AS
$BODY$
BEGIN
   RETURN QUERY
    SELECT current_query 
    FROM pg_stat_activity 
    <strike>ORDER BY length(current_query) DESC LIMIT 1;</strike>
    where current_query ilike 'select * from f_snitch%';
    -- much more reliable solution

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

  select * from f_snitch('koper') AS (tt text);

And here is the result:

enter image description here

It's probably not 100% reliable solution but for small systems (for few users) it's quite ok.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656814

TG_NAME and friends are special variables that only exist for trigger functions. Regular plpgsql functions don't have anything like that. I am fresh out of ideas how you could possibly get this inside the called function in plpgsql.

You could add RAISE NOTICE to your function so you get the desired information

CREATE OR REPLACE FUNCTION f_snitch(text)
  RETURNS text LANGUAGE plpgsql AS
$func$
BEGIN
    RAISE NOTICE '.. from f_snitch.';
    -- do stuff
    RETURN 'Snitch says hi!';
END
$func$;

Call:

SELECT f_snitch('foo')

In addition to the result, this returns a notice:

NOTICE:  .. from f_snitch.

Fails to please in two respects:

  1. Calling statement is not in the notice.
  2. No CONTEXT in the notice.

For 1. you can use RAISE LOG instead (or set your cluster up to log NOTICES, too - which I usually don't, too verbose for me). With standard settings, you get an additional line with the STATEMENT in the database log:

LOG:  .. from f_snitch.
STATEMENT:  SELECT f_snitch('foo')

For 2., have a look at this related question at dba.SE. CONTEXT would look like:

CONTEXT:  SQL statement "SELECT f_raise('LOG', 'My message')"
    PL/pgSQL function "f_snitch" line 5 at PERFORM

Upvotes: 1

Related Questions