PostgreSQL Immutable function usage

Question

I'm trying to fasten posgresql selection using a function by means of saying it's immutable or stable, so I have a function

CREATE OR REPLACE FUNCTION get_data(uid uuid)
RETURNS integer AS $$
BEGIN
    RAISE NOTICE 'UUID %', $1;
-- DO SOME STUFF
    RETURN 0;

END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

When I call it like:

SELECT get_data('3642e529-b098-4db4-b7e7-6bb62f8dcbba'::uuid)
  FROM table
 WHERE true LIMIT 100;

I have 100 results and only one notice raised

When I call it this way:

SELECT get_data(table.hash)
  FROM table
 WHERE 1 = 1 AND table.hash = '3642e529-b098-4db4-b7e7-6bb62f8dcbba' LIMIT 100;

I have 100 result and 100 notices raised

the condition (table.hash = '3642e529-b098-4db4-b7e7-6bb62f8dcbba') added to make sure that the in param is the same

table.hash is uuid type

The questions is: So how can force PG to some how cache the result of the function? ( if it's possibe ) I want to have only one notice ( function call ) be raised in the second case...

Why are you trying to pipe output via RAISE NOTICE? What are you actually trying to accomplish? — user554546
– user554546, Commented Dec 26, 2012 at 14:37

Erwin Brandstetter · Accepted Answer · 2012-12-26 14:47:11Z

2

In your first example get_data('3642e529-b098-4db4-b7e7-6bb62f8dcbba'::uuid) is a constant, independent of table rows, so it is evaluated once.

In the second example get_data(table.hash) is functionally depending on a column value, therefore it is evaluated once per row.

If you want to evaluate the function once, it cannot depend on a value from a column (when more than one row is processed).

After discussion in comments, here is an example how to call function only once per hash:

SELECT *, get_data(x.hash) AS some_data_once_per_hash
FROM    (
    SELECT hash, count(*) AS ct
    FROM   table
    WHERE  table.hash = '3642e529-b098-4db4-b7e7-6bb62f8dcbba'
    GROUP  BY 1
    ) x

edited Dec 26, 2012 at 14:47

answered Dec 26, 2012 at 14:35

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

13 Comments

Dmitri Gudkov Over a year ago

So there is no way to make PG to cache the result?

Erwin Brandstetter Over a year ago

@Good.Dima: Well, your first example would be the way, although it's not "caching" per se.

user554546 Over a year ago

@Good.Dima - Why do you want to dump out the same string 100 times?

Dmitri Gudkov Over a year ago

@JackManey I need to aggregate diff uids, and I want to call function once per uid...

Erwin Brandstetter Over a year ago

@Good.Dima: I suppose you start a new question where you post your complete query.

|

Clodoaldo Neto · Accepted Answer · 2012-12-26 14:57:19Z

0

If Erwin's answer is not good for your case you can either create a materialized view or a trigger to update a "computed column"

answered Dec 26, 2012 at 14:57

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Immutable function usage

2 Answers 2

13 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related