从PostgreSQL函数中选择以返回复合类型 [英] Select from PostgreSQL function that returns composite type

问题描述

如何在SELECT中包括一个返回复合类型的函数?
我有复合类型:

CREATE TYPE public.dm_nameid AS (
  id   public.dm_int,
  name public.dm_str
);

此外，我还有一个返回此类型 fn_GetLinkedProject(integer) 的函数. 我需要做这样的事情:

SELECT 
    p.id, p.data, p.name, 
    pl.id linked_id, pl.name linked_name
FROM tb_projects p
   left join "fn_GetLinkedProject"(p.id) pl

我该怎么做?

我已阅读这篇文章.

我不想要以下方法:

SELECT
 p.id, p.data, p.name, 
    (select pl1.id from "fn_GetLinkedProject"(p.id) pl1 ) linked_id,
    (select pl2.name from "fn_GetLinkedProject"(p.id) pl2 ) linked_name
FROM tb_projects p

解决方案

Postgres 9.3或更高版本

使用什么是和PostgreSQL中的子查询之间有什么区别?

Postgres 9.2或更早版本

劣势有几个原因.附加列别名并不是那么简单.而是重命名其他有冲突的名称:

SELECT p.id AS p_id, p.data AS p_data, p.name AS p_name, (fn_getlinkedproject(p.id)).*
FROM   tb_projects p;

结果:

 p_id | p_data | p_name | id |  name
------+--------+--------+----+--------
    1 | data_1 | name_1 |  2 | name_2
    2 | data_2 | name_2 |  3 | name_3
    3 | data_3 | name_3 |  1 | name_1

要重命名结果列，您必须:

SELECT p.id, p.data, p.name
     ,(fn_getlinkedproject(p.id)).id   AS linked_id
     ,(fn_getlinkedproject(p.id)).name AS linked_name
FROM   tb_projects p;

两个旧解决方案都解决了重复调用该函数的相同(较差)查询计划.

为避免这种情况，请使用子查询:

SELECT p.id, p.data, p.name
    , (p.x).id AS linked_id, (p.x).name AS linked_name
FROM  (SELECT *, fn_getlinkedproject(id) AS x FROM tb_projects) p;

请注意必需的括号的位置.
阅读关于复合类型的手册.

演示

CREATE TYPE dm_nameid AS (
  id   int
, name text); -- types simplified for demo

CREATE TABLE tb_projects(
  id   int
, data text
, name text);

INSERT INTO tb_projects VALUES
  (1, 'data_1', 'name_1')
, (2, 'data_2', 'name_2')
, (3, 'data_3', 'name_3');

CREATE function fn_getlinkedproject(integer)  -- avoiding CaMeL-case for demo
  RETURNS dm_nameid LANGUAGE sql AS
'SELECT id, name FROM tb_projects WHERE id = ($1 % 3) + 1';

db<>小提琴此处

How to include a function that returns a composite type in a SELECT?
I have composite type:

CREATE TYPE public.dm_nameid AS (
  id   public.dm_int,
  name public.dm_str
);

Also, I have a function that returns this type fn_GetLinkedProject(integer). And I need to make something like this:

SELECT 
    p.id, p.data, p.name, 
    pl.id linked_id, pl.name linked_name
FROM tb_projects p
   left join "fn_GetLinkedProject"(p.id) pl

How can I do this?

I have read this article.

I don't want following method:

SELECT
 p.id, p.data, p.name, 
    (select pl1.id from "fn_GetLinkedProject"(p.id) pl1 ) linked_id,
    (select pl2.name from "fn_GetLinkedProject"(p.id) pl2 ) linked_name
FROM tb_projects p

解决方案

Postgres 9.3 or later

Use a LATERAL join!

SELECT p.id, p.name, p.data, f.*
FROM   tb_projects p
LEFT   JOIN LATERAL fn_getlinkedproject(p.id) f(linked_id, lined_name) ON TRUE;

Result:

 id |  data  |  name  | linked_id | linked_name
----+--------+--------+-----------+-------------
  1 | data_1 | name_1 |         2 | name_2
  2 | data_2 | name_2 |         3 | name_3
  3 | data_3 | name_3 |         1 | name_1

See:

• What is the difference between LATERAL and a subquery in PostgreSQL?

Postgres 9.2 or older

Inferior for several reasons. Attaching column aliases is not as simple. Rather rename other conflicting names:

SELECT p.id AS p_id, p.data AS p_data, p.name AS p_name, (fn_getlinkedproject(p.id)).*
FROM   tb_projects p;

Result:

 p_id | p_data | p_name | id |  name
------+--------+--------+----+--------
    1 | data_1 | name_1 |  2 | name_2
    2 | data_2 | name_2 |  3 | name_3
    3 | data_3 | name_3 |  1 | name_1

To rename the result columns, you have to:

SELECT p.id, p.data, p.name
     ,(fn_getlinkedproject(p.id)).id   AS linked_id
     ,(fn_getlinkedproject(p.id)).name AS linked_name
FROM   tb_projects p;

Both old solutions resolve to the same (poor) query plan of calling the function repeatedly.

To avoid that, use a subquery:

SELECT p.id, p.data, p.name
    , (p.x).id AS linked_id, (p.x).name AS linked_name
FROM  (SELECT *, fn_getlinkedproject(id) AS x FROM tb_projects) p;

Note the placement of essential parentheses.
Read the manual about composite types.

Demo

CREATE TYPE dm_nameid AS (
  id   int
, name text); -- types simplified for demo

CREATE TABLE tb_projects(
  id   int
, data text
, name text);

INSERT INTO tb_projects VALUES
  (1, 'data_1', 'name_1')
, (2, 'data_2', 'name_2')
, (3, 'data_3', 'name_3');

CREATE function fn_getlinkedproject(integer)  -- avoiding CaMeL-case for demo
  RETURNS dm_nameid LANGUAGE sql AS
'SELECT id, name FROM tb_projects WHERE id = ($1 % 3) + 1';

db<>fiddle here

这篇关于从PostgreSQL函数中选择以返回复合类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！