From: | "Bui, Michelle P" <michelle(dot)p(dot)bui(at)boeing(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "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 03:06:19 |
Message-ID: | 06F83FEE7BBD0049AD97C7D4159C5A6F31A9B882@XCH-PHX-101.sw.nos.boeing.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks Tom and Adrian - Here are my SQL and the function:
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 derived_table
GROUP BY category, Status
And here is the function:
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;
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;
-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, April 01, 2014 7:21 PM
To: Bui, Michelle P
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] SQL works but same function is confused
"Bui, Michelle P" <michelle(dot)p(dot)bui(at)boeing(dot)com> writes:
> I have this query that when executed as a SQL statement, it works perfect!
OK ...
> I used <<block>> before Declare section and try using block.v_status
> but this is not allowed (has syntax error).
That should work (in the sense of not getting a syntax error), but since you've not shown us exactly what you wrote, it's impossible to say what you did wrong. However, that's mostly beside the point, because ...
> 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.
According to the above, the behavior you want is use_column. Why are you trying to get it to use the variable when that's not what you want?
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2014-04-02 03:56:07 | Re: SQL works but same function is confused |
Previous Message | Andrew Sullivan | 2014-04-02 02:45:19 | Re: simple update query stuck |