Postgres - How to use variables to call a function and returns a table/rowset from DBeaver/pgAdmin

Question

I'm new to Postgres. I tried to run the following in pgAdmin/DBeaver but getting "ERROR: query has no destination for result data" error

do $$
declare customerid integer := 151;
begin
SELECT * FROM get_orders(customerid);
end $$

My guts tell me that it is something simple. What do I need to change so it will display the results in DBeaver or pgAdmin?
I don't want something like this:

SELECT * FROM get_orders(151);

I do want to use something like a variable to separate from the actual select statement. Thanks.

script segments to prepare the table/function

------------------
CREATE TABLE orders
(
    id integer,
    customerid INTEGER,
    description varchar(100)
)
------------------
INSERT INTO Orders VALUES
(1,101, 'Test Order 1'),
(2,151, 'Random Order')

------------------
CREATE OR REPLACE FUNCTION get_orders (p_customerid int) 
    RETURNS TABLE (
        id integer,
    customerid INTEGER,
    description varchar(100)
) 
AS $$
BEGIN
    RETURN QUERY SELECT
        *
    FROM
        orders ord
    WHERE
        ord.customerid = p_customerid;
END; $$
LANGUAGE 'plpgsql';

"I don't want something like this: `SELECT * FROM get_orders(151);`" -- Then why don't you simply do *this*, without any `DO` block? — sticky bit, Jun 09 '20 at 22:59
https://stackoverflow.com/search?q=[postgresql]+variables is helpful especially this one: https://stackoverflow.com/questions/1490942/how-to-declare-a-variable-in-a-postgresql-query — Daniel.LQ, Jun 10 '20 at 13:41

score 0 · Answer 1 · answered Jun 10 '20 at 05:45

SQL is different from other programming languages:

It has no concept of execution order, so the idea of first setting a variable and then using it is alien to SQL.
SQL has no concept of “variables” in the first place.
In SQL, a “program” is a single statement.

So, SQL is lacking when it comes to variables and procedural programming. The remedy is to use a function in a procedural language like PL/pgSQL. Then you can use variables, and you can return the output as function result.

The DO statement has no way of returning results.

score 0 · Answer 2 · answered Jun 10 '20 at 13:50

with the help of this: How to declare a variable in a PostgreSQL query.

I guess DO statement can't return results. Here are alternatives:

DO $$
    declare customerid integer := 151;
BEGIN
    CREATE TEMP TABLE tmp_orders ON COMMIT DROP AS
        SELECT * FROM get_orders(customerid);
END $$;
SELECT * FROM tmp_orders;

-----------------------------
PREPARE temp(int) AS
    SELECT * FROM get_orders($1);
EXECUTE temp(151)

-----------------------------
WITH
    custid AS (VALUES (151))
SELECT * FROM get_orders((table custid))

-- DBeaver Only
@set custid = 151
SELECT * FROM get_orders(${custid});

Postgres - How to use variables to call a function and returns a table/rowset from DBeaver/pgAdmin

2 Answers2