如何从一个函数返回表中的各个列？ [英] How to get individual columns from table returned from a function?

问题描述

我试图用返回表做一个函数。

I'm trying to make a function with returns table.

CREATE  FUNCTION karta_pacjenta(pe VARCHAR(11))
  RETURNS TABLE('data' DATE,'imie' TEXT, 'nazwisko' TEXT, 'diagnoza' TEXT,'przepisany lek' TEXT)  AS'  
BEGIN
 RETURN QUERY SELECT w.dzien AS dzien,p.imie, p.nazwisko, ch.nazwa, l.nazwa
 FROM pacjenci p, diagnozy d, choroby ch, wizyty w, leki l, recepty r
 WHERE p.pesel=d.pesel AND d.kod_choroby=ch.kod_choroby AND p.pesel=pe AND w.pesel=pe AND l.kod_leku=r.kod_leku AND r.nr_wizyty=w.nr_wizyty;
END;
' LANGUAGE 'plpgsql';

它工作相当不错，但我需要一个东西。作为这个函数的结果，我得到函数名，然后几个记录。

It works quite nice, but I need one more thing. As result of this function I get function name, and then couple of records.

但是，我想有记录的列名。任何想法如何做这个？

But, I'd like to have column names over the records. Any idea how to do this?

推荐答案

要分解从函数中得到的行就像任何其他表： / p>

To decompose the rows you get back from the function treat it like any other table:

SELECT * FROM karta_pacjenta('foo45678901');

返回一组行的函数也称为表函数。

Functions returning a set of rows are also called "table functions".

除此之外，您所提供的内容将无法使用。

Aside from that, what you presented wouldn't work.

CREATE  FUNCTION karta_pacjenta(_pe varchar)
  RETURNS TABLE(data DATE, imie TEXT, nazwisko TEXT
              , diagnoza TEXT,przepisany lek TEXT)  AS
$func$
SELECT w.dzien, p.imie, p.nazwisko, ch.nazwa, l.nazwa
FROM   pacjenci  p
JOIN   diagnozy  d  USING (pesel) -- shorthand if columns are unambiguous
JOIN   wizyty    w  USING (pesel)
JOIN   choroby   ch ON ch.kod_choroby = d.kod_choroby
JOIN   recepty   r  ON r.nr_wizyty = w.nr_wizyty
JOIN   leki      l  ON l.kod_leku = r.kod_leku 
WHERE  p.pesel = _pe
$func$ LANGUAGE sql;

• 列名的单引号是语法错误。必须是双引号。

  • Single quotes for column names are a syntax error. Would have to be double-quotes. Better you always use unquoted, legal, lower case names, though.

    不要引用语言名称，它是一个标识符。

    Don't quote the language name, it's an identifier.

    其余部分是可选的，但建议很好。

    The rest is optional, but good advise.

    
    <

    使用显式JOIN语法。

    Use explicit JOIN syntax. Same result, but much easier to maintain.

    使用 varchar（11）作为参数类型的 varchar 或 text

    It's probably pointless to use varchar(11) instead of just varchar or text as param type. (Corner case exceptions apply.)

    使用美元引用 - 这在这里是完全可选的，但通常是引用函数体的好样式。迟早或之后您需要在正文中加入单引号。

    Use dollar-quoting - which is totally optional here, but generally good style to quote the function body. Sooner or later you'll want to include single quotes in the body.

    这篇关于如何从一个函数返回表中的各个列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！