Re: frustrated by plpgsql procedure

On Mar 27, 2006, at 2:10 PM, Dino Vliet wrote:

> Can somebody tell me why my location variable is NOT
> working as expected? I would like to use it in a loop
> to create multiple text files which names would be
> different because of the way I concatenate it with the
> looping variable.

You can't just stick an arbitrary string in the middle of a SQL
statement. You can build a SQL statement and then run it with EXECUTE.

Try something like this:

create or replace function doedit() returns varchar AS $$
/* Procedure to create textfile from database table.
*/

DECLARE
i integer := 340;
start date :='2004-08-06';
eind date :='2004-08-12';
location varchar(30) :='/usr/Data/plpgtrainin';

BEGIN
create table cancel as (SOME QUERY);
location := location || i || '.txt' ::varchar(30);
raise notice 'location is here %', location;
execute 'copy cancel to ' || location || ' with delimiter as \',\'
null as \'.\'';
return location;
END;
$$ Language plpgsql;

Also note you must have super user access to use COPY, so it still
might fail if you don't have the right privileges.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL