Using variables from parent in nested postgresql function?

Question

I'm have a pretty long SQL routine that gets a few parameters and runs through a whole bunch of CASE statements. I have the following structure:

DO $$
DECLARE var INTEGER = "someColumn" FROM TABLE LIMIT 1;

BEGIN;

CREATE OR REPLACE FUNCTION pg_temp.fn(var2 boolean) returns decimal AS 
$fn$ SELECT CASE WHEN var2 THEN var::decimal ELSE 0::decimal END $fn$ language sql;
$$

And using var inside of fn does not seem to quite work. As in the column does not exist. Is there a way to make it work, am I maybe thinking too complicated?

Edit: fixed the type that was missing. In the original code there is a type declaration, the declaration is not the problem. Using the variable in the nested function is.

Dan · Accepted Answer · 2018-02-01 19:41:02Z

1

First of all. THIS IS NOT THE BEST PRACTICE. It can be done, but it is not recommended to use nested functions.

The problem is that var is in a different scope than the function. You have to "write" the function as an independent unit. You can do this with EXECUTE and FORMAT.

Here is an example of a function that shows var as a message:

DO $$
DECLARE
    sql text; -- To "write" the function
    var integer := id FROM table_name ORDER BY id LIMIT 1;
BEGIN
-- To make it hapend I use $test$ to set the function.
-- FORMAT is to allocate var into the function through '%s'.
-- you have to use the correct sintax acording to the data type.
    sql :=  FORMAT($test$
CREATE OR REPLACE FUNCTION test() RETURNS void AS $FUNCTION$
BEGIN
    RAISE NOTICE '%s';
END; $FUNCTION$ LANGUAGE plpgsql
$test$,var);
    EXECUTE sql;
END; $$

edited Feb 1, 2018 at 19:41

answered Feb 1, 2018 at 18:19

Dan

1,9211 gold badge16 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

david1602 Over a year ago

That was an error of mine. My actual declaration contains a type (I can't post the original code here) but when trying to CREATE the FUNCTION, the column var in my example does not exist. The variable works elsewhere in the script, it's just in that function that it's unknown.

Dan Over a year ago

Is the CREATE FUNCTION inside an EXECUTE?

david1602 Over a year ago

Nope, no EXECUTE in my code. Edit: everything else is pure SQL, using the declared variables at the top (there are 8 of them).

Dan Over a year ago

@david1602 Changed my answer, i think is what you need.

david1602 Over a year ago

I see, thank you for the efforts! It seems waaay too complicated. I know SQL isn't an actual "programming language", but I thought the stuff with the different scopes might work, similar to it working in JavaScript. Anyway, I think I'm going to opt for just selecting it in a different CTE and joining it, rather than trying to obtain the number via a function. Edit: I sadly cannot vote because I have less than 15 reputation, but I marked it as accepted solution at least!

Collectives™ on Stack Overflow

Using variables from parent in nested postgresql function?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related