sql - PostgreSQL 的函数/子查询表

Question

使用 PostgresSQL 9.3，OSX。

我已经破解了下面的功能。
令人震惊的是，它可以正常工作并返回一个基于位置的邮政编码列表。

我现在想要 for/loop 遍历这些结果，为每个返回单个多列表的邮政编码运行选择查询。将以下 SELECT 查询合并到此函数中的最佳方法是什么？

SELECT "NPI" FROM doc
WHERE "Provider Business Mailing Address Postal Code"='<ZIPCODERESULTS FROM BELOW>'; 

CREATE FUNCTION get_npizips (text) RETURNS TABLE(a char(5)) AS '
  DECLARE
    -- Declare aliases for user input.
    npi_id ALIAS FOR $1;
    -- Declare a variable to hold the zipcode
    zipcode varchar;

  BEGIN
    SELECT INTO zipcode substring(provider_nodes.address_postal_code for 5) FROM provider_nodes
      WHERE provider_nodes.npi = npi_id;
    RETURN QUERY SELECT zcta FROM zctas WHERE ST_DWithin(
    geom,
    (
        SELECT geom
        FROM zctas
        WHERE zcta = zipcode
    ),
    7 * 1.6 * 1000
);
   END;
'LANGUAGE 'plpgsql';

Answer 1

它所有都归结为这个简化的查询:

SELECT d."NPI"
FROM   provider_nodes p
JOIN   zctas z0 ON  z0.zcta = left(p.address_postal_code, 5)
JOIN   zctas z  ON  ST_DWithin(z.geom, z0.geom, 7 * 1.6 * 1000)
JOIN   doc   d  ON "Provider Business Mailing Address Postal Code" = z.zcta
WHERE  p.npi = <your npi_id here>;

您的函数有多个缺点。 如果你想额外保留它，我建议:

CREATE FUNCTION get_npizips (npi_id text)
  RETURNS SETOF text AS
$func$
   SELECT z.zcta
   FROM   provider_nodes p
   JOIN   zctas z0 ON  z0.zcta = left(p.address_postal_code, 5)
   JOIN   zctas z  ON  ST_DWithin(z.geom, z0.geom, 7 * 1.6 * 1000)
   WHERE  p.npi = $1
$func$ LANGUAGE sql;

• 对函数体使用美元引号。
• 不要引用语言名称plpgsql。
• 不鼓励使用 ALIAS。使用现代风格的命名参数。
• 不需要 plpgsql。一个 sql 函数可以完成这项工作。