Is it possible to use a variable and not specify the type of the return value in postgreSQL?

Question

Consider this T-SQL:

DECLARE @ColorID INT
SET @ColorID = 3

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

This works, but does not 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$$;

He wants me to define a set of results. I do not want to do this because it continues to change as I am just studying the data.

ERROR:  query has no destination for result data

I tried the "return query" but then got the following error:

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

So, I want to return a few lines without specifying what the result set should look like. Using PostgreSQL 9.4.4

Answer 1

(DO ) , Postgres . . .

(WITH )

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

:

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;

:

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 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();