Ok thanks for now. I understood the problem and what I should do to fix it. Will try that later. Thanks for all the tips and the REALLY FAST answers!! --- John DeSoi <desoi@xxxxxxxxxx> wrote: > > 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 > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
