Is it possible to use a variable and not specify a

Question

Consider this T-SQL:

DECLARE @ColorID INT
SET @ColorID = 3

SELECT *, left(name,3) 
FROM Products p
WHERE ColorID = @ColorID

This works but doesn't declare a variable:

SELECT *, substring(name,1,3) 
FROM Products p
WHERE ColorID = 3

I tried this:

DO $$
DECLARE ColorID INT;
BEGIN
    ColorID := 3;

    SELECT *, substring(name,1,3) 
    FROM Products p
    WHERE ColorID = ColorID 
END$$;

It wants me to specify the result set. I don't want to do that because it keeps changing as I'm just exploring the data.

ERROR:  query has no destination for result data

I tried "return query" but then get this error:

ERROR:  cannot use RETURN QUERY in a non-SETOF function

So I want to return multiple rows without specifying what the result set should look like. Using PostgreSQL 9.4.4

Answer 1

Anonymous code block (DO command) cannot return rows and Postgres has no global variables. There are few ways to live without it. Four of them are as follows.

Use common table expression (WITH command)

WITH def AS (
    SELECT 3 AS colorid
    )
SELECT *, substring(name,1,3) 
FROM products
JOIN def
USING (colorid);

Use a temporary table for variables:

CREATE TEMP TABLE var(colorid int);
INSERT INTO var values (3);
SELECT *, substring(name,1,3) 
FROM products
JOIN var
USING (colorid);
DROP TABLE var;

Use a temporary table for results:

DO $$
DECLARE v_colorid INT;
BEGIN
    v_colorid := 3;
    CREATE TEMP TABLE res AS 
        SELECT *, substring(name,1,3) 
        FROM products
        WHERE colorid = v_colorid;
END $$;
SELECT * 
FROM res;
DROP TABLE res;

Create a function (example):

CREATE OR REPLACE FUNCTION select_from_products()
RETURNS TABLE (colorid int, name text, abbr text)
LANGUAGE plpgsql as $$
DECLARE v_colorid INT;
BEGIN
    v_colorid := 3;
    RETURN QUERY
        SELECT *, substring(p.name,1,3) 
        FROM products p
        WHERE p.colorid = v_colorid;
END $$;

SELECT * FROM select_from_products();