SQL works but same function is confused
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).
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
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;
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
/CREATE OR REPLACE FUNCTION get_status/
/RETURNS TABLE (category SMALLINT, status TEXT, tool_count BIGINT) AS/
/$BODY$/
//
#variable_conflict use_variable
/DECLARE/
/change this to "s_status TEXT;"/ I think it's conflicting with the
column alias of same
Show quoted text
//
/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;/
Thanks in advance for your insight or suggestion!
Michelle
On 04/01/2014 06: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)./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/
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;/
/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
Then again, do you use "v_status" as a variable? select into v_status....
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@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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.klaver@aklaver.com]
Sent: Tuesday, April 01, 2014 07:29 PM
To: Bui, Michelle P; pgsql-general@postgresql.org <pgsql-general@postgresql.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.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
"Bui, Michelle P" <michelle.p.bui@boeing.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 04/01/2014 06:44 PM, Bui, Michelle P wrote:
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.
Not use v_status where, in the DECLARE block or as an alias in the query?
If it where me and I wanted to keep the variable in the DECLARE block I
would do:
DECLARE
_v_status TEXT;
At least you would be able to track which variable is in play.
Can we assign value for an alias in a function?
An SQL alias for table, column, etc or an alias for a function argument?
http://www.postgresql.org/docs/9.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIAS
Thanks,
Michelle
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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@sss.pgh.pa.us]
Sent: Tuesday, April 01, 2014 7:21 PM
To: Bui, Michelle P
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] SQL works but same function is confused
"Bui, Michelle P" <michelle.p.bui@boeing.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Bui, Michelle P wrote
#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
Seriously? Just pick a different alias for the 'active/inactive' column in
the sub-query. Problem solved.
Or, even smarter, don't even declare the variable since you never actually
use it anywhere in the function...
The variable_conflict variable should generally be used for backward
compatibility and not for newly coded functions. For those just choose
names that do not conflict. The exception is for function return names that
you want to match existing column names in which case you need to prefix
appropriately.
You may want to provide your attempt to name the block to see if we can
figure why it gave a syntax error.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/SQL-works-but-same-function-is-confused-tp5798277p5798298.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general