Running a SELECT in a PL/pgSQL block

Question

I am newbie to PostgreSQL and I have to use inline queries in SSRS report to fetch data from PostgreSQL DB.

The scenario is: based on selected value of the report parameter I need to get output from different tables. Please see the below sample inline query.

    DO
    $do$

    BEGIN
    IF ($1 = 'Monthly') THEN

    SELECT *
    FROM table1;

    ELSE 

    SELECT *
    FROM table2;

    END IF;

    END
    $do$

The above query is giving error,

ERROR: query has no destination for result data SQL state: 42601 Hint: If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function inline_code_block line 6 at SQL statement

Please note that I cannot use Stored procedures or Functions to retrieve the required data and I have to use inline queries only.

Can some one let me know of how to solve above error?

Answer 1

Your examples has two issues - DO statement (anonymous block) doesn't support

1. parameters
2. returning result.

PostgreSQL doesn't support techniques used in T-SQL or MS-SQL called unbound queries. Every query has to have specified target. You can use a function instead (table1 and table2 should to have same structure):

CREATE OR REPLACE FUNCTION foo(frequency)
RETURNS SETOF table1 AS $$
BEGIN
  IF $1 = 'Monthly' THEN
    RETURN QUERY SELECT * FROM table1;
  ELSE
    RETURN QUERY SELECT * FROM table2;
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM foo('Monthly');

Answer 2

Assuming the tables have the same column structure you can use a union to do both options in a single query.

SELECT * FROM table1 WHERE $1 = 'Monthly'
UNION ALL
SELECT * FROM table2 WHERE NOT ($1 = 'Monthly')