是否可以使用变量而不在PostgreSQL中指定返回类型? [英] Is it possible to use a variable and not specify a return type in postgreSQL?

问题描述

考虑以下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

我尝试过:

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

因此，我想返回多行而不指定结果集的外观.使用PostgreSQL 9.4.4

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

推荐答案

匿名代码块( DO命令)无法返回行，并且Postgres没有全局变量. 没有它，生活的方法很少.其中四个如下.

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.

使用公用表表达式( WITH命令)

Use common table expression (WITH command)

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

这篇关于是否可以使用变量而不在PostgreSQL中指定返回类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！