Issue with Retool Database (Beta) when using variables, set, and select

I'm new to Retool, trying to test a simple app using the Retool database beta and came across this error: Query run failed. * * message:"syntax error at or near "integer"" when running the following query. This query should return the number of rows in the table.

DECLARE counter integer;
SET counter = SELECT COUNT(*) "tblJobOrders"

According to the standard PostgreSQL documentation the above syntax should be correct.

Could anyone help me identify what I may be doing wrong?

Thanks for the help

Hey Carlos! Welcome to the community :-).

Could you elaborate a bit on your use case - what are you trying to achieve where a declarative is needed? It's a legacy thing from earlier SQL database and is rarely the most performant option in 2023 :-).

Jonathan

I'm trying find out how many rows are in a table in order to use that value in a while loop. the idea is that the while loop will be performed until it reaches the value contain in the counter variable.

Sorry, for posting twice. I was not sure that first post was successful due an internet interruption.

Thank you for reaching out

Hey @Carlos_Liendo!

Wrapping DECLARE statements and loops in a DO $$ .. END $$ block seems to work in the following case (based on the "Postgresql while loop update" section of this doc):

DO $$
DECLARE 
colum_no int :=1;
total_rows int :=(select count(*) from color);
BEGIN
  WHILE colum_no < total_rows LOOP
  UPDATE color
  SET color_name = UPPER(color_name)
  WHERE color_id = colum_no;
  colum_no := colum_no+1;
  END LOOP;
END $$;

Would you mind explaining a bit more about what you're trying to do with your loop? There may be another way to approach this as well if you're trying to do an operation for each row of your table!