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 t
LANGUAGE plpgsql;
-Original Message-
From: Tom Lane [mailto:t...@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" writes:
> I have t
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 sam
"Bui, Michelle P" writes:
> I have this query that when executed as a SQL statement, it works perfect!
OK ...
> I used <> 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
yo
nks,
Michelle
Can we assign value for an alias in a function?
- Original Message -
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Tuesday, April 01, 2014 07:29 PM
To: Bui, Michelle P; pgsql-general@postgresql.org
Subject: Re: [GENERAL] SQL works but same function is confused
On
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
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 'inac
/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
//
/v_status TEXT;/
/BEGIN/
/
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
