postgres - How to assign a result count to a variable and do a select query based on that

Question

I am a from T-SQL and MS SQL Server background and struggling with PostgreSQL. I need to declare a variable, do a count query, save the result of the count in the variable; then based, on the count assign a date to another variable, and then do a select query with that assigned date to return its result set. The problem is when I declare a variable without a DO $$ block, like so:

DECLARE num_rows bigint; I get:

ERROR: syntax error at or near "bigint"

LINE 1: DECLARE num_rows bigint;

And if I try within the DO $$ block, I get the following error on the SELECT:

ERROR: query has no destination for result data

HINT: If you want to discard the results of a SELECT, use PERFORM instead.

CONTEXT: PL/pgSQL function inline_code_block line 35 at SQL statement SQL state: 42601

This is what I am trying:

DO $$
DECLARE num_rows bigint;
DECLARE end_date timestamp with time zone;
BEGIN
SELECT COUNT(my_table.id) 
INTO num_rows
FROM my_table
WHERE my_table.something = 1;

IF num_rows > 500 THEN
end_date = '2022-12-03';
END IF;

SELECT * FROM another_table WHERE some_date < end_date;

END $$;

Is there any way to accomplish this or similar in PostgreSQL? I cannot use functions because it is a legacy database and I cannot do DDL changes to it.

But you don't really need variables to achieve what you want. — user330315
– user330315, Commented Jan 27, 2023 at 12:49
Thank you for sharing the links. I have seen them before but most of them suggest using a function, which I am constrained not to use. CTE is another option but it doesn't let me specify something like "use this date if the count exceeds x, otherwise use that date". — Abubakar Mehmood
– Abubakar Mehmood, Commented Jan 27, 2023 at 13:13

mikasa · Accepted Answer · 2023-01-27 12:53:24Z

1

1)in row end_date = '2022-12-03' you need a semicolon

2)in last select statement you must use execute

I think this will work:

DO $$
DECLARE 
  num_rows bigint;
  end_date timestamp with time zone;
BEGIN
SELECT COUNT(my_table.id) 
INTO num_rows
FROM my_table
WHERE my_table.something = 1;

IF num_rows > 500 THEN
end_date = '2022-12-03';
END IF;

execute 'SELECT * FROM another_table WHERE some_date <'|| end_date;

END $$;

You can also try to run something like this:

with mydate as(
    select case when (select count(*) from mytable where something = 1)>500 then '2022-12-03' end as end_date,
    (select count(*) from mytable where something = 1) as num_rows
    )
select * from another_table a,mydate b where a.some_date>end_date;

edited Jan 27, 2023 at 12:53

answered Jan 27, 2023 at 12:13

mikasa

18010 bronze badges

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

7 Comments

Abubakar Mehmood Over a year ago

Thank you for the speedy reply. Terribly sorry that semicolon was a copy-past error. I have amended it now. I will try the execute way and let you know. But is there another way where I won't need to form/run a dynamic query?

Abubakar Mehmood Over a year ago

Unfortunately, doing execute runs the query but does not return the result of the select. In pgAdmin, I see "Query returned successfully" in the "Messages" panel and "No data output. Execute a query to get output" in the "Data output" panel.

mikasa Over a year ago

this will work if you create a function that returns a query. I now realized that you just want to run a query. But it's not quite clear why you need to use a parameter for a static number like the count of rows and a date. I mean the last select is from another table. Can you please give some more information about what you need to do?

user330315 Over a year ago

@AbubakarMehmood: if that is used for paging, then I would expect some kind of application code behind where you can easily store the result of a query in a variable. Why do everything in SQL?

Abubakar Mehmood Over a year ago

@mikasa Thanks a lot. The CTE approach worked but instead of joining with mydate, I used a subquery to select end_date from it like @a_horse_with_no_name suggested in his comment on the OP because it was much faster.

|

Collectives™ on Stack Overflow

postgres - How to assign a result count to a variable and do a select query based on that

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related