PLPGSQL problem with SELECT INTO
"Jay O'Connor" <joconnor@cybermesa.com> writes:
CREATE FUNCTION countRows (varchar, varchar) RETURNS int AS
'
DECLARE
val1 ALIAS FOR $1;
val2 ALIAS FOR $2;
total int;
BEGIN
SELECT INTO total count(*) FROM mytable WHERE fieldone =
val1 AND fieldtwo = val2;
RETURN total;
END;
' LANGUAGE PLPGSQL;
The value returned is much higher. Actaully, it is exactly what the number
should be without the AND query. No matter what I'. passing for the second
variable, I get the same result (even if it's a value not in that column
for any record)
I kinda suspect you have an unexpected variable substitution. Is the
above *exactly* how the offending function reads, or have you
editorialized on the names used?
regards, tom lane
Import Notes
Reply to msg id not found: 20030529145321.A1144@altaicaReference msg id not found: 20030529145321.A1144@altaica
OK, I'm trying to count the number of records based on two criteria, this
works at the psql prompt but not in a plpgsql function
SELECT count(*) FROM mytable WHERE fieldone = 'val1' AND fieldtwo =
'val2';
This gives me back '4' which is what I expect (trust me :)
but if I try to put this in a PLPGSQL function, it doesn't work.
CREATE FUNCTION countRows (varchar, varchar) RETURNS int AS
'
DECLARE
val1 ALIAS FOR $1;
val2 ALIAS FOR $2;
total int;
BEGIN
SELECT INTO total count(*) FROM mytable WHERE fieldone =
val1 AND fieldtwo = val2;
RETURN total;
END;
' LANGUAGE PLPGSQL;
The value returned is much higher. Actaully, it is exactly what the number
should be without the AND query. No matter what I'. passing for the second
variable, I get the same result (even if it's a value not in that column
for any record)
Amy thoughts as to why this might be?
I'm using this in a procedure that will eventually delete certain rows from
a table and needs to decerement a field in another table based on how many
rows will be deleted? Can I just do a DELETE and use GET DIAGNOSTICS to
get the number of rows that were deleted?
Take care,
Jay
OK, I'm trying to count the number of records based on two criteria, this
works at the psql prompt but not in a plpgsql functionSELECT count(*) FROM mytable WHERE fieldone = 'val1' AND fieldtwo
=
'val2';This gives me back '4' which is what I expect (trust me :)
but if I try to put this in a PLPGSQL function, it doesn't work.
CREATE FUNCTION countRows (varchar, varchar) RETURNS int AS
'
DECLARE
val1 ALIAS FOR $1;
val2 ALIAS FOR $2;
total int;
BEGIN
SELECT INTO total count(*) FROM mytable WHERE fieldone =
val1 AND fieldtwo = val2;
RETURN total;
END;
' LANGUAGE PLPGSQL;
After some furtehr research I determined that it was ignoring the fieldTwo
check completely. I finally narrowerd it ddown because my code read
fieldTwo ALIAS FOR $2;
and the query read
SELECT .....fieldtwo = fieldTwo
And plpgsql was not being case sensitive and thought it was comparing it to
itself.
Take care,
Jay
On 2003.05.29 14:41 Tom Lane wrote:
I kinda suspect you have an unexpected variable substitution. Is the
above *exactly* how the offending function reads, or have you
editorialized on the names used?
It was editorialized and in the editorializing I obscured the real issue.
I had a local variable name and a column name in my query as the same name,
differenitated only by case. Unfortunately, plpgsql did not see them as
different :)
Take care,
Jay