Thread: function volatility

function volatility

From

Date:

22 November 2010, 10:44:58

How to declare function that return set of data from tables? for example :

CREATE TYPE "public"."cst_test" AS (
"code" VARCHAR(10),
"name" VARCHAR(50),
"quantity" NUMERIC(10,2) );

CREATE OR REPLACE FUNCTION public."sf_test"(id_code VARCHAR)
RETURNS SETOF "public"."cst_test" AS
$body$
SELECT
t1.code,
t1.name,
t2.quantity
FROM "public"."test_1" t1
INNER JOIN "public"."test_2" t2 ON t1.id_test1 = t2.id_test1
WHERE t1.code = $1
$body$
LANGUAGE 'sql' ;

It's STABLE, IMMUTABLE OR VOLATILE?

Re: function volatility

From

Leif Biberg Kristensen

Date:

22 November 2010, 11:08:30

On Monday 22. November 2010 12.30.31 maps@navigator-info.com wrote:
>
> How to declare function that return set of data from tables? for
> example :
>
>
> CREATE TYPE "public"."cst_test" AS (
>   "code" VARCHAR(10),
>   "name" VARCHAR(50),
>   "quantity" NUMERIC(10,2) );
>
> CREATE OR REPLACE FUNCTION public."sf_test"(id_code VARCHAR)
>   RETURNS SETOF "public"."cst_test" AS
> $body$
> SELECT
>   t1.code,
>   t1.name,
>   t2.quantity
> FROM "public"."test_1" t1
>   INNER JOIN "public"."test_2" t2 ON t1.id_test1 = t2.id_test1
> WHERE t1.code = $1
> $body$
> LANGUAGE 'sql' ;
>
>
> It's STABLE, IMMUTABLE OR VOLATILE?
>

A function that modifies the database is VOLATILE.
A function that doesn't modify the database, but does database lookups,
is STABLE.
A function that doesn't modify the database, and doesn't do database
lookups (like SQRT(x)) is IMMUTABLE.

regards,
Leif B. Kristensen