Re: Function returning subset of columns from table (return type)

On Feb 3, 2008 3:23 PM, brian <brian(at)zijn-digital(dot)com> wrote:

> CREATE TYPE your_type
> AS (
> added CHAR(11) NOT NULL,
> updated CHAR(11) NOT NULL,
> text_col TEXT
> );
>
> CREATE FUNCTION get_note(id INT)
> RETURNS SETOF your_type IMMUTABLE
> AS $$
>
> DECLARE
> your_row your_type%rowtype;
>
> BEGIN
> SELECT INTO your_row
> to_char(added, 'Mon D YYYY'),
> to_char(updated, 'Mon D YYYY'),
> text_col
> FROM note
> WHERE id = $1;
>
> RETURN your_row;
> END;
> $$ LANGUAGE sql;
>
>
All's okay, except you should not have declared it IMMUTABLE, because the
results depend on a database query.

From the docs:
IMMUTABLE indicates that the function cannot modify the database and always
returns the same result when given the same argument values; that is, it does
not do database lookups or otherwise use information not directly present in
its argument list

Best regards,
--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *

http://gurjeet.frihost.net

Mail sent from my BlackLaptop device