1

I am new to creating functions in postgresql. The version that I'm using is rather old. It's 8.2.15 (not my choice, but my org's). The following example is trying to apply one function to a temp table in another function.

-- First function 
create or replace function inner_func(_tbl anyelement)
RETURNS void AS
$$
BEGIN
  EXECUTE 'ALTER TABLE ' || _tbl || ' ADD COLUMN d_amount INTEGER';
  EXECUTE 'UPDATE ' || _tbl || ' SET d_amount = 2* amount';
  RETURN;
END;
$$
LANGUAGE plpgsql volatile;

-- Second function
CREATE OR REPLACE FUNCTION outer_func()
RETURNS void AS
$$
BEGIN
  DROP TABLE IF EXISTS my_temp;
  CREATE TEMP TABLE my_temp
  (id serial primary key,
   amount integer
  );
  INSERT into my_temp (amount) values (10),(20);

  -- now apply the inner_func right here
  EXECUTE 'SELECT inner_func(' || quote_ident('my_temp') || ')';

  RETURN;
END;
LANGUAGE plpgsql volatile;

When I run

SELECT outer_func();

It spits out an ERROR:

column "my_temp" does not exist

But the inner_func works if I use it on its own like the following:

create temp table my_temp2
(id serial primary key,
 amount integer
);
INSERT INTO my_temp2 (amount) values (10),(20);
SELECT inner_func(quote_ident('my_temp2'));
SELECT * from my_temp2;

id amount d_amount
1    10      20
2    20      40

How can I make this inner_func work inside outer_func? Any idea?

1 Answer 1

1

It looks like the problem is here:

EXECUTE 'SELECT inner_func(' || quote_ident('my_temp') || ')';

=> 

EXECUTE 'SELECT inner_func(quote_ident(' || quote_literal('my_temp') || '));';

DBFiddle Demo

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

1 Comment

@midtownguru Glad to hear I could help :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.