Home Return table in function postgres
Reply: 1

Return table in function postgres

Anif Maulana
1#
Anif Maulana Published in 2018-02-14 06:50:45Z

When trying to return table in postgres, this my query:

CREATE OR REPLACE FUNCTION list_log_approval(IN p_create_code INTEGER,IN p_update_code INTEGER) RETURNS
TABLE(processcode integer, processname VARCHAR,id BIGINT, pleader CHARACTER VARYING,activity VARCHAR,date_plead timestamp)
LANGUAGE plpgsql AS $$ 
BEGIN
        IF NOT EXISTS( SELECT * FROM log_approval WHERE processcode = $1 and status = 'A' or status = 'D')AND NOT EXISTS(SELECT * FROM log_approval WHERE processcode = $2 and status = 'A' or status = 'D') THEN
                RETURN QUERY SELECT * from vw_list_appv;
        END IF;
    RETURN;
END $$;

-- i call like this

select * from list_log_approval(1070,1072)

I get the following error:

[Err] ERROR: column reference "processcode" is ambiguous
LINE 3: processcode**

why is it ambiguous?

Laurenz Albe
2#
Laurenz Albe Reply to 2018-02-14 07:15:16Z

processcode is used both as function parameter and as table column.

The best thing is to use function parameters with a different name, like p_processcode.

But you can also disambiguate by qualifying the name: log_approval.processcode for the column and list_log_approval.processcode for the function parameter.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.312628 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO