Thread: 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
change this to "s_status TEXT;" I think it's conflicting with the column alias of sameRETURNS 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
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
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 erroroccurred. 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
"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
likePostgresql 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
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 erroroccurred. 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
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'simpossible 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
likePostgresql 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'snot what you want?
regards, tom lane
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.