Re: [GENERAL] stable function called for every row?

2009-01-07 Thread Tom Lane
Gerhard Heift  writes:
> The function is called for every row in the table if it is stable or
> volatile and only once if it is immutable?

Yes, possibly.

> I thought it had to be called only once, if it is stable.

No.  Stable means that it is *okay* to call it only once per query,
not that that is *guaranteed* to happen.  (What it really does is give
the planner license to use an indexscan on a condition involving the
function --- an indexscan's comparison value is evaluated just once,
so it would give the wrong answers for a non-stable function.)

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] stable function called for every row?

2009-01-07 Thread Pavel Stehule
Hello,

stable, volatile, immutable flag doesn't necessary means caching or
not caching.

if you need really only one call, use srf function

postgres=# create table foo(a int);
CREATE TABLE
postgres=# insert into foo values(10),(20);
INSERT 0 2
postgres=# create function foof(bool) returns setof int as $$begin
raise notice 'foof call'; if $1 then return next 1; else return next
0; end if; return; end;$$ language plpgsql;
CREATE FUNCTION
postgres=# select * from foof(true);
NOTICE:  foof call
 foof
--
1
(1 row)


postgres=# select * from foo,foof(true);
NOTICE:  foof call
 a  | foof
+--
 10 |1
 20 |1
(2 rows)

regards
Pavel Stehule

2009/1/7 Gerhard Heift :
> I isolated my problem a little bit:
>
> CREATE FUNCTION get_array() RETURNS integer[] AS
> $BODY$
> BEGIN
>  RAISE INFO 'get_array';
>  RETURN ARRAY[1, 2];
> END
> $BODY$ LANGUAGE 'plpgsql' STABLE;
>
> And now
>
> SELECT * FROM generate_series(1,3) a(b) where array[b] <@ core.get_array();
>
> gives me:
>
> INFO:  get_array
> INFO:  get_array
> INFO:  get_array
>  b
> ---
>  1
>  2
> (2 rows)
>
> Why?? Wlli functions which returns an array not be cached?
>
> Regards,
>  Gerhard
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFJZQ2Ea8fhU24j2fkRAlChAKCDTbhPdKxschTqScfhqRb5olvQ5wCcCcgl
> iMUlTPHTmX0jX/G84Pk82iA=
> =b/pY
> -END PGP SIGNATURE-
>
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] stable function called for every row?

2009-01-07 Thread Gerhard Heift
I isolated my problem a little bit:

CREATE FUNCTION get_array() RETURNS integer[] AS
$BODY$
BEGIN
  RAISE INFO 'get_array';
  RETURN ARRAY[1, 2];
END
$BODY$ LANGUAGE 'plpgsql' STABLE;

And now

SELECT * FROM generate_series(1,3) a(b) where array[b] <@ core.get_array();

gives me:

INFO:  get_array
INFO:  get_array
INFO:  get_array
 b 
---
 1
 2
(2 rows)

Why?? Wlli functions which returns an array not be cached?

Regards,
  Gerhard


signature.asc
Description: Digital signature


[GENERAL] stable function called for every row?

2009-01-07 Thread Gerhard Heift
Hello,

I have a query like this:

SELECT * FROM table WHERE has_permission('permission_name');

and the function

CREATE FUNCTION has_permission(IN pname text, OUT is_ok boolean) RETURNING 
boolean AS
$BODY$
BEGIN
  SELECT has_perm INTO is_ok FROM permission WHERE title = pname;

  RAISE INFO 'function called';

  IF is_ok IS FALSE THEN
-- this function is VOLATILE
PERFORM log.exception('permission denied');
  END IF;

  RETURN;
END
$BODY$ LANGUAGE 'plpgsql' STABLE SECURITY DEFINER;

The function is called for every row in the table if it is stable or
volatile and only once if it is immutable?

I thought it had to be called only once, if it is stable.

(PostgreSQL 8.3.5)

Regards,
 Gerhard


signature.asc
Description: Digital signature