Update Query not working inside postgresql function but it works fine outside function

Question

I am creating function in postgresql to update table. when I ran single function it works fine but I use same query inside function it wont works.

update house1 SET calendar=
 'H'||' '||house_num::text||' '||left(to_char(sdate, 'Mon'),2) ||' to '  || to_char(sdate, 'DD') ||to_char(rdate, 'Mon-DD')
 where id=1

Inside function I wrote following query

begin
execute
'UPDATE house1 SET calendar = '
||quote_nullable( 'H')||' '||house_num||' '||left(to_char(sdate, 'Mon'),2) ||' to '  || to_char(sdate, 'DD') ||to_char(rdate, 'Mon-DD')
|| ' WHERE' ||  'stage'
'>=' || quote_nullable(0) ;
END

It generates following error

ERROR: column "house_num" does not exist.

Please help me

I don't think you need a dynamic statement in your function : EXECUTE (...). Just try to insert the first UPDATE statement in your function body and share the result. — Edouard
– Edouard, Commented Jan 8, 2022 at 7:45

Pooya · Accepted Answer · 2022-01-08 08:01:17Z

1

Why do you use the execute statement in function?

You can use update statement directly in function (When using the execute statement if you need to build dynamic query)

create or replace function function_name()
   returns void 
   language plpgsql
  as
$$
begin
 update house1 SET calendar=
 'H'||' '||house_num::text||' '||left(to_char(sdate, 'Mon'),2) ||' to '  || to_char(sdate, 'DD') ||to_char(rdate, 'Mon-DD')
 where stage >= 0;
end;
$$;

select function_name();

answered Jan 8, 2022 at 8:01

Pooya

3,2032 gold badges16 silver badges21 bronze badges

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

1 Comment

Stream Over a year ago

execute 'UPDATE ' || table_name_||' ' || 'SET ' || quote_ident(calCol) || '=' || quote_nullable(calConcat) || ' ' 'H' || house_num::text || ' S' || stage::text || ' ' || left(to_char(sdate, 'Mon'),2) ||' to ' || to_char(sdate, 'DD') || to_char(rdate, 'Mon-DD') || 'WHERE ' || quote_ident(calcol) || '>=' || quote_nullable(refCol); --'>=' || 0 ; but I want to create this function with dynamic parameter

Collectives™ on Stack Overflow

Update Query not working inside postgresql function but it works fine outside 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