Re: SQL works but same function is confused - Mailing list 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'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