Обсуждение: declare variable in udf
Hello,
How do I declare a variable after BEGIN?
I want to declare it in the if statement.
DROP FUNCTION IF EXISTS tmp_test(integer);
CREATE FUNCTION tmp_test(
p_id integer
)
RETURNS text
AS $$
DECLARE the_return_value text;
BEGIN
DROP TABLE IF EXISTS temp_test_table;
CREATE TEMP TABLE temp_test_table(
some_value text
);
INSERT INTO temp_test_table
SELECT data FROM table WHERE id=p_id;
SELECT INTO the_return_value some_value FROM temp_test_table;
IF 1=1 THEN
--how do I declare a variable here? it this possible?
RAISE NOTICE 'this is a notice';
END IF;
RETURN the_return_value;
END;
$$ language plpgsql
Best,
Peter
On 21/11/2012 11:42, Peter Kroon wrote: > Hello, > > How do I declare a variable after BEGIN? > I want to declare it in the if statement. > > DROP FUNCTION IF EXISTS tmp_test(integer); > CREATE FUNCTION tmp_test( > p_id integer > ) > RETURNS text > AS $$ > DECLARE the_return_value text; > BEGIN > DROP TABLE IF EXISTS temp_test_table; > CREATE TEMP TABLE temp_test_table( > some_value text > ); > INSERT INTO temp_test_table > SELECT data FROM table WHERE id=p_id; > SELECT INTO the_return_value some_value FROM temp_test_table; > > IF 1=1 THEN > --how do I declare a variable here? it this possible? > RAISE NOTICE 'this is a notice'; > END IF; > > RETURN the_return_value; > > END; > $$ language plpgsql I don't think that is possible - you have to declare all your variables in the DECLARE section. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
So, multiple DECLARE sections are not allowed?
2012/11/21 Raymond O'Donnell <rod@iol.ie>
I don't think that is possible - you have to declare all your variablesOn 21/11/2012 11:42, Peter Kroon wrote:
> Hello,
>
> How do I declare a variable after BEGIN?
> I want to declare it in the if statement.
>
> DROP FUNCTION IF EXISTS tmp_test(integer);
> CREATE FUNCTION tmp_test(
> p_id integer
> )
> RETURNS text
> AS $$
> DECLARE the_return_value text;
> BEGIN
> DROP TABLE IF EXISTS temp_test_table;
> CREATE TEMP TABLE temp_test_table(
> some_value text
> );
> INSERT INTO temp_test_table
> SELECT data FROM table WHERE id=p_id;
> SELECT INTO the_return_value some_value FROM temp_test_table;
>
> IF 1=1 THEN
> --how do I declare a variable here? it this possible?
> RAISE NOTICE 'this is a notice';
> END IF;
>
> RETURN the_return_value;
>
> END;
> $$ language plpgsql
in the DECLARE section.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
2012/11/21 Peter Kroon <plakroon@gmail.com>: > So, multiple DECLARE sections are not allowed? it is not allowed in plpgsql resp. DECLARE is related to block - if you use nested block, then you can use nested DECLAREs Regards Pavel Stehule > > > 2012/11/21 Raymond O'Donnell <rod@iol.ie> >> >> On 21/11/2012 11:42, Peter Kroon wrote: >> > Hello, >> > >> > How do I declare a variable after BEGIN? >> > I want to declare it in the if statement. >> > >> > DROP FUNCTION IF EXISTS tmp_test(integer); >> > CREATE FUNCTION tmp_test( >> > p_id integer >> > ) >> > RETURNS text >> > AS $$ >> > DECLARE the_return_value text; >> > BEGIN >> > DROP TABLE IF EXISTS temp_test_table; >> > CREATE TEMP TABLE temp_test_table( >> > some_value text >> > ); >> > INSERT INTO temp_test_table >> > SELECT data FROM table WHERE id=p_id; >> > SELECT INTO the_return_value some_value FROM temp_test_table; >> > >> > IF 1=1 THEN >> > --how do I declare a variable here? it this possible? >> > RAISE NOTICE 'this is a notice'; >> > END IF; >> > >> > RETURN the_return_value; >> > >> > END; >> > $$ language plpgsql >> >> I don't think that is possible - you have to declare all your variables >> in the DECLARE section. >> >> Ray. >> >> -- >> Raymond O'Donnell :: Galway :: Ireland >> rod@iol.ie > >
On 21/11/2012 12:01, Peter Kroon wrote: > So, multiple DECLARE sections are not allowed? Actually, I just had a quick look here - http://www.postgresql.org/docs/9.2/static/plpgsql-structure.html - and it seems that you can have multiple declare - begin - end blocks. Try it and see! Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie