From: | "Bui, Michelle P" <michelle(dot)p(dot)bui(at)boeing(dot)com> |
---|---|
To: | "'adrian(dot)klaver(at)aklaver(dot)com'" <adrian(dot)klaver(at)aklaver(dot)com>, "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: SQL works but same function is confused |
Date: | 2014-04-02 01:44:41 |
Message-ID: | 06F83FEE7BBD0049AD97C7D4159C5A6F31A9B844@XCH-PHX-101.sw.nos.boeing.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com]
Sent: Tuesday, April 01, 2014 07:29 PM
To: Bui, Michelle P; pgsql-general(at)postgresql(dot)org <pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] 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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-04-02 02:20:30 | Re: SQL works but same function is confused |
Previous Message | Adrian Klaver | 2014-04-02 00:29:54 | Re: SQL works but same function is confused |