v_status is not used inthe SQL statement but is used in the function because we need to return a table (catgory, tool_count, status). Even when I did not use v_status, and assign value 'valid' or 'invalid' to the output parameter directly, the same error occurred. Thanks, Michelle Can we assign value for an alias in a function? ----- Original Message ----- From: Adrian Klaver [mailto:adrian.klaver@xxxxxxxxxxx] Sent: Tuesday, April 01, 2014 07:29 PM To: Bui, Michelle P; pgsql-general@xxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxx> Subject: Re: SQL works but same function is confused On 04/01/2014 05:10 PM, Bui, Michelle P wrote: > Hi all, > > I have this query that when executed as a SQL statement, it works > perfect! The table tools contains many records in a time series, with > attributes like category but without the field status. I assign the > value of status ’active’ or ‘inactive’ depending on whether the tool > record exists after a certain time (number of seconds). > > > However, when I write a function to return the same result, using this > SQL statement, and I declare a local variable v_status TEXT; it errors > out when executed. The key complaint is: > > ERROR: column reference "v_status" is ambiguous... > > DETAIL: It could refer to either a PL/pgSQL variable or a table column. > > The function returns table (category, status, and tool_count) using > RETURN QUERY in front of the query. > > I used <<block>> before Declare section and try using block.v_status but > this is not allowed (has syntax error). When I use #variable_conflict > use_variable, there is no error anymore, but the resulted Status field > is null. Seemed like Postgresql does not assign the v_status as we wish. > > /CREATE OR REPLACE FUNCTION get_status/ > > /RETURNS TABLE (category SMALLINT, status TEXT, tool_count BIGINT) AS/ > > /$BODY$/ > > // > > #variable_conflict use_variable > > /DECLARE/ > > /v_status TEXT;/ I am not seeing where v_status is being used. Below it is an alias name in the query, which is where the conflict is coming in. > > /BEGIN/ > > / RETURN QUERY SELECT category, v_status as status, count (tool_id) > AS tool_count/ > > / FROM / > > / (SELECT distinct category, tool_id, ‘active’ as v_status/ > > /FROM tools/ > > / WHERE time >= 123456/ > > / UNION/ > > / SELECT distinct e1.category, e1.tool_id, ‘inactive’ as > v_status/ > > / FROM tools e1/ > > / WHERE not exists/ > > / (SELECT e2.category, e2.tool_id/ > > / FROM tools e2/ > > / WHERE e2.sim_time >= 123456/ > > / AND e2.category = e1.category/ > > / AND e2.tool_id = e1.tool_id)/ > > / ) AS derivedTable/ > > /GROUP BY category, Status;/ > > // > > /END; $BODY$/ > > /LANGUAGE plpgsql;/ > > Thanks in advance for your insight or suggestion! > > Michelle > -- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
