column does not exist error when using a variable in postgresql copy in function

Question

I have this postgresql function

CREATE OR REPLACE FUNCTION sp_select_test(_id bigint)
  RETURNS void AS
$BODY$                

BEGIN
        copy (select sub.id
        from (
          SELECT id FROM sim s where id = _id) sub) TO '/tmp/results.tsv';
END;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION sp_select_test(bigint)
OWNER TO postgres;

When I run

select * from sp_select_test(264);

I get error - column "_id" does not exist

If I replace the variable _id in the function with a value say 264, the function works. That is if instead of id = _id I use id = 264

Any reason why the function is failing?

try $1 instead of _id

Vao Tsun
– Vao Tsun

2015-06-23 09:51:17 +00:00
Commented Jun 23, 2015 at 9:51 — Vao Tsun
– Vao Tsun, Commented Jun 23, 2015 at 9:51
Get "there is no parameter $1" message

Kevin Joymungol
– Kevin Joymungol

2015-06-23 09:58:07 +00:00
Commented Jun 23, 2015 at 9:58 — Kevin Joymungol
– Kevin Joymungol, Commented Jun 23, 2015 at 9:58
ah, I see now :) use dynamic query instead

Vao Tsun
– Vao Tsun

2015-06-23 10:30:47 +00:00
Commented Jun 23, 2015 at 10:30 — Vao Tsun
– Vao Tsun, Commented Jun 23, 2015 at 10:30

Vao Tsun · Accepted Answer · 2015-06-23 10:30:26Z

2

CREATE OR REPLACE FUNCTION sp_select_test(_id bigint)
  RETURNS void AS
$BODY$                

BEGIN
        execute $$copy (select sub.id
        from (
          SELECT id FROM sim s where id = $$||_id||$$) sub) TO '/tmp/results.tsv'$$;
END;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION sp_select_test(bigint)
OWNER TO postgres;

answered Jun 23, 2015 at 10:30

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

1 Comment

Quakumei Over a year ago

What's the logic behind that?

Collectives™ on Stack Overflow

column does not exist error when using a variable in postgresql copy in function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related