LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)
-   -   Postgresql Newbie needs help with function (https://www.linuxquestions.org/questions/linux-server-73/postgresql-newbie-needs-help-with-function-890244/)

mpapet 07-06-2011 10:02 AM

Postgresql Newbie needs help with function
 
I've been using scripts to perform data manipulation for a while now and I'd like to take a stab at writing functions.

I've got a table with addresses and I'd like to loop through the table and do some calculating on the fields.

##############
CREATE OR REPLACE FUNCTION dev.mygeocode_function()
RETURNS SETOF text AS
$BODY$
DECLARE
r tiger._stores_grocery%rowtype; --r becomes a quoted string?
franistan varchar;
BEGIN
for r in select '' || address || ' ' || city || ', ' || state || ' ' || zip || '' as the_address from tiger._stores_grocery limit 10 LOOP
SELECT g.rating FROM tiger.geocode(r) As g into franistan;
return next franistan;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
############

doing select * from dev.mygeocode_function() returns an error. r is populated with the string _stores_grocery, not the 'the_address' field.

What am I doing wrong?


All times are GMT -5. The time now is 11:24 PM.