postgresql - PostgreSQL 9.5: Return columns based on input parameter

Question

I have the following table.

Table:

CREATE TABLE tblTest
(
    Column1 int,
    Column2 int,
    Column3 int,
    Column11 int,
    Column111 int,
    Column1111 int,
    Column22 int,
    Column222 int,
    Column33 int
);

Records:

INSERT INTO tblTest VALUES(1,2,3,11,111,1111,22,222,33);

I am writting FUNCTION to return the result from above table based on passed parameter.

The parameter p_ColumnName is used to pass the column name. Based on column name the list of columns needs to be display.

Function:

CREATE OR REPLACE FUNCTION ufn_test
(
    p_ColumnName text
)
RETURNS -- ? How to specify the dynamic return type or dynamic column list ?
AS

$BODY$

DECLARE v_ColumnsList text;
    v_query text;
BEGIN

    IF p_ColumnName = 'Column1'
    THEN
        v_ColumnsList := 'Column11,Column111,Column1111';

    ELSIF p_ColumnName = 'Column2'
    THEN
        v_ColumnsList := 'Column22,Column222';

    ELSIF p_ColumnName = 'Column3'
    THEN
        v_ColumnsList := 'Column33';

    END IF;

    v_query := 'SELECT '|| v_ColumnsList ||' FROM tblTest';

    RETURN QUERY EXECUTE v_query;

END;

$BODY$

LANGUAGE PLPGSQL;

标签： postgresqlpostgresql-9.5

Answer 1

我建议您在 Returns中使用类型RefCursor 。

根据您使用的语言（SQL、Java ...），您可以从此 Cursor 获取数据。

因此，您的功能将变为

CREATE OR REPLACE FUNCTION ufn_test( p_ColumnName text )
RETURNS refcursor AS
$BODY$
DECLARE 
    v_ColumnsList text;
    v_query text;
    ref_cursor refcursor;
BEGIN

    IF (p_ColumnName = 'Column1') THEN
        v_ColumnsList := 'Column11,Column111,Column1111';
    ELSIF (p_ColumnName = 'Column2') THEN
        v_ColumnsList := 'Column22,Column222';
    ELSIF (p_ColumnName = 'Column3') THEN
        v_ColumnsList := 'Column33';
    END IF;

    v_query := 'SELECT '|| v_ColumnsList ||' FROM tblTest';

    OPEN ref_cursor FOR EXECUTE (v_query);

    RETURN ref_cursor;

END;
$BODY$
LANGUAGE plpgsql;

希望它会帮助你。