Thread: variables in sql functions
Is there a way for something like that to be made with sql function not plpgsql CREATE FUNCTION resource_limit($int4, date) RETURNS BOOLEAN AS ' SELECT SUM(amount) as $user_amount FROM user_resource WHERE id = $1; SELECT SUM(amount) > ($user_amount) FROM resource WHERE enddate > $2 ' LANGUAGE 'sql' WITH (isstrict); It is a reduced version, but it gives the idea of what I need - I need to select a constant for the duration of the function data as a variable and use it for the later query.
I think you can use SELECT INTO <variable here>. I read that in the online version of the Practical PostgreSQL book. Otis ceco@noxis.net (ceco) wrote in message news:<533d2efe.0202220313.79cb0e2d@posting.google.com>... > Is there a way for something like that to be made with sql function > not plpgsql > > CREATE FUNCTION resource_limit($int4, date) RETURNS BOOLEAN > AS ' > SELECT SUM(amount) as $user_amount FROM user_resource WHERE id = $1; > SELECT SUM(amount) > ($user_amount) FROM resource WHERE enddate > $2 > ' > LANGUAGE 'sql' > WITH (isstrict); > > > It is a reduced version, but it gives the idea of what I need - I need > to select a constant for the duration of the function data as a > variable and use it for the later query.