Postgres using clause in stored procedure/function not working

Question

I have a stored procedure in which, through dynamic SQL, I want to delete rows where energy = 0. This works allright as long as I use string concatenation for the query build-up. But when I try to use the 'USING' clause it gives an error. (see below for code and error) The procedure compiles alright but I get a run-time error. I've tried to find on-line what was wrong but cannot find it ... Any hints / solutions much appreciated.

The error I get:

ERROR: syntax error at or near "$1" where: PL/pgSQL function myf_proc(text) line 8 at EXECUTE

    drop table if exists mysolar;
    
    create table mysolar (
        time        timestamptz not null,
        energy      double precision,
        eenergy     double precision,
        ienergy     double precision
    );
    
    select create_hypertable('mysolar',by_range('time'));
    
    insert into mysolar(time,energy,eenergy,ienergy)
    values
    ('2023-02-24 17:20:33',22.13299942,0.273999989,21.8589992),
    ('2023-02-24 17:21:33',22.13500023,0.273999989,21.8610000),
    ('2023-02-24 17:22:33',22.13699913,0.273999989,21.8630008),
    ('2023-02-24 17:23:33',22.13899994,0.273999989,21.8649997),
    ('2023-02-24 17:24:33',22.14299965,0.273999989,21.8689994),
    ('2023-02-24 17:25:33',0,0,0),
    ('2023-02-24 17:26:33',22.15099907,0.273999989,21.8770008),
    ('2023-02-24 17:27:33',22.15600014,0.273999989,21.8819999),
    ('2023-02-24 17:28:33',22.15999985,0.273999989,21.8859996),
    ('2023-02-24 17:29:33',22.16300011,0.273999989,21.8889999),
    ('2023-02-24 17:30:33',22.16500092,0.273999989,21.8910007),
    ('2023-02-24 17:31:33',22.16699982,0.273999989,21.8929996),
    ('2023-02-24 17:32:33',22.16900063,0.273999989,21.8950004),
    ('2023-02-24 17:34:12',22.17099953,0.273999989,21.8969993),
    ('2023-02-24 17:35:12',22.17300034,0.273999989,21.8990001),
    ('2023-02-24 17:36:12',22.17399979,0.273999989,21.8999996),
    ('2023-02-24 17:37:12',22.17499924,0.273999989,21.9009990),
    ('2023-02-24 17:38:12',22.17700005,0.273999989,21.9029998),
    ('2023-02-24 17:39:12',22.1779995,0.273999989,21.9039993),
    ('2023-02-24 17:40:12',22.17900085,0.273999989,21.9050006);

    create or replace procedure myf_proc(tblname text)
    language plpgsql
    as
    $$
    declare
        tablename text := tblname;
        qry text;
    begin
        --qry := 'delete from ' || tablename || ' where energy = 0;'; --this works ok
        qry := 'delete from $1 where energy = 0;';
        execute qry using tablename;
        --raise notice 'QUERY :> %', qry;
    end;
    $$;
    
    call myf_proc('"mysolar"');

execute format('delete from %I where energy = 0;',tablebame); the example with using tablename would inject single- or dollar-quoted string with escapes, whereas the %I format placeholder uses double quoting, if necessary. Here's the doc with details on what needs to be quoted and how. Your first, commented out example would break if you gave it a table name with a space, mixed case letters or some special characters in it, the %I handles all that automatically. — Zegarek
– Zegarek, Commented May 30 at 10:03
Using this pattern is just asking to be hacked. Don't escalate data to code. — MatBailie
– MatBailie, Commented May 30 at 18:31
I knew my code was not ok, that's why I wanted to improve on it. — Leo
– Leo, Commented May 31 at 7:01

Zegarek · Accepted Answer · 2025-05-30 11:20:40Z

3

Your first, commented out example would break if you gave it a table name with a space, mixed case letters or some special characters in it. Use format() with a %I placeholder to handle all that automatically:
_{demo at db<>fiddle}

select count(*)from mysolar where energy=0;

count
1

create or replace procedure myf_proc(tblname text)
language plpgsql as $p$
begin execute format('delete from %I where energy = 0;', tblname);
end $p$;
    
call myf_proc('mysolar');
select count(*)from mysolar where energy=0;

count
0

The example with using tablename would bind a single- or dollar-quoted string constant with escapes, whereas %I uses double quotes or no quotes, as required by identifiers:

I treats the argument value as an SQL identifier, double-quoting it if necessary. It is an error for the value to be null (equivalent to quote_ident).

Here's the doc with details on what needs to be quoted and how.

edited May 30 at 11:20

answered May 30 at 10:20

Zegarek

29.9k5 gold badges27 silver badges32 bronze badges

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

1 Comment

Leo May 31 at 7:00

Tkx for the help. I will look into that.

Collectives™ on Stack Overflow

Postgres using clause in stored procedure/function not working

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