SQL bind error, 6 parameters but requires 0

Support
#1

I'm getting this error when I try to run a query that I've successfully confirms works outside of retool (all parameters in the highlight feature in retool check out as well):

bind message supplies 6 parameters, but prepared statement "" requires 0

DO $$
    BEGIN
        IF EXISTS (
            SELECT * FROM retailer_sku
            WHERE retailer_id={{table1.selectedRow.data.id}}
            AND sku_id={{input2.value}}
        )
        then
            update retailer_sku
            set active=TRUE
            WHERE retailer_id={{table1.selectedRow.data.id}}
            AND sku_id={{input2.value}}
        ELSE
            insert into retailer_sku(retailer_id, sku_id, price)
            values ({{table1.selectedRow.data.id}}, {{input2.value)}}, 2000);
        END IF ;
    END
$$ ;
#2

I'm facing the same issue. Did anyone fix this for you @paul ?

#3

@karthik no word yet, I ended up making an api endpoint to resolve my issue, but I'd love for there to be a solution with SQL.

#4

@justin @william-retool Do you have any idea why this is happening? I've noticed a similar error when I try writing a function for postgres:

do $$
declare
    i int := 0;
    rows_to_insert int := {{inputAddRunCont.value}};
begin
    while @i < rows_to_insert loop
        INSERT INTO container(run_id) VALUES ({{addRun.data.id[0]}});
        i := i + 1;
        end loop;
end $$;

but getting error

bind message supplies 2 parameters, but prepared statement "" requires 0

#5

HI Paul, I think you might be better off defining that function as a stored procedure directly in Postgres, and then just executing the stored procedures from Retool.

To provide a little more context, we're currently transforming your SQL query into this:

do $$
declare
    i int := 0;
    rows_to_insert int := ?;
begin
    while @i < rows_to_insert loop
        INSERT INTO container(run_id) VALUES (?);
        i := i + 1;
        end loop;
end $$;

with the parameters

[inputAddRunCont.value, addRun.data.id[0]]

However, you can't use ? inside postgres functions, and so it doesn't work.

1 Like
#6

Gotcha, thanks for the reply.