0

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.

Abubakar Mehmood
  • 938
  • 1
  • 10
  • 19
  • https://stackoverflow.com/search?q=[postgresql]++query+has+no+destination+for+result+data –  Jan 27 '23 at 12:42
  • https://stackoverflow.com/questions/1490942/how-to-declare-a-variable-in-a-postgresql-query?r=SearchResults&s=1%7C136.4549 –  Jan 27 '23 at 12:43
  • 1
    But you don't really need variables to achieve what you want. –  Jan 27 '23 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 Jan 27 '23 at 13:13
  • 1
    Sure it's possible: https://dbfiddle.uk/0uXanIYd –  Jan 27 '23 at 13:17
  • @a_horse_with_no_name Yes, it does! Thanks a bunch for the query! – Abubakar Mehmood Feb 09 '23 at 07:42

1 Answers1

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;
mikasa
  • 170
  • 10
  • 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 Jan 27 '23 at 12:27
  • 1
    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. – Abubakar Mehmood Jan 27 '23 at 12:35
  • 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? – mikasa Jan 27 '23 at 12:46
  • I am trying to cut short the date range in the interest of performance. 500, in this case, is my page limit. So, if I can find 500 records in the first five days of the specified date range, I want to avoid searching the whole date range. I've simplified the example in the question to keep it focussed. – Abubakar Mehmood Jan 27 '23 at 12:58
  • 1
    @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? –  Jan 27 '23 at 13:26
  • @a_horse_with_no_name Yeah, that makes sense. Thanks for pointing me in the right direction. I did a mix of the CTE approach and shifting some logic to the application code. – Abubakar Mehmood Feb 09 '23 at 07:40
  • 1
    @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. – Abubakar Mehmood Feb 09 '23 at 07:41