Обсуждение: frustrated by plpgsql procedure
Hi guys, I trying for days to get this simple plpgsql procedure to run but I keep getting this error: psql:pgsql_procedure.txt:15: ERROR: syntax error at or near at character 17 QUERY: copy cancel TO $1 with delimiter as ',' null as '.' CONTEXT: SQL statement in PL/PgSQL function doedit near line 12 psql:pgsql_procedure.txt:15: LINE 1: copy cancel TO $1 with delimiter as ',' null as '.' psql:pgsql_procedure.txt:15: The source code of this plpgsql procedure is: create or replace function doedit() returns void 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; copy cancel TO location with delimiter as ',' null as '.' ; END $$ Language plpgsql; 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. Hope somebody can help me because it's a big frustration. Thanks in advanced. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Dino Vliet wrote: > Hi guys, > > I trying for days to get this simple plpgsql procedure > to run but I keep getting this error: > > psql:pgsql_procedure.txt:15: ERROR: syntax error at > or near at character 17 > QUERY: copy cancel TO $1 with delimiter as ',' null > as '.' > CONTEXT: SQL statement in PL/PgSQL function doedit > near line 12 > psql:pgsql_procedure.txt:15: LINE 1: copy cancel TO $1 > with delimiter as ',' null as '.' > psql:pgsql_procedure.txt:15: > > The source code of this plpgsql procedure is: > > create or replace function doedit() returns void 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; > copy cancel TO location with delimiter as ',' null as > '.' ; > END > $$ Language plpgsql; > Missing semi-colon after END?
Nope:-( I added it just now and still the same error message!! --- Bricklen Anderson <banderson@presinet.com> wrote: > Dino Vliet wrote: > > Hi guys, > > > > I trying for days to get this simple plpgsql > procedure > > to run but I keep getting this error: > > > > psql:pgsql_procedure.txt:15: ERROR: syntax error > at > > or near at character 17 > > QUERY: copy cancel TO $1 with delimiter as ',' > null > > as '.' > > CONTEXT: SQL statement in PL/PgSQL function > doedit > > near line 12 > > psql:pgsql_procedure.txt:15: LINE 1: copy cancel > TO $1 > > with delimiter as ',' null as '.' > > psql:pgsql_procedure.txt:15: > > > > The source code of this plpgsql procedure is: > > > > create or replace function doedit() returns void > 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; > > copy cancel TO location with delimiter as ',' null > as > > '.' ; > > END > > $$ Language plpgsql; > > > Missing semi-colon after END? > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Dino Vliet wrote: > Nope:-( > > I added it just now and still the same error message!! Have you tried it with your "copy" command executed dynamically? eg. execute 'copy cancel to location ...';
Dino Vliet <dino_vliet@yahoo.com> writes:
> I trying for days to get this simple plpgsql procedure
> to run but I keep getting this error:
You can't ordinarily use a variable to supply a table (or field) name
in a plpgsql query; that doesn't work because plpgsql wants to cache a
plan for the query. A workaround is to build the query as a string
value, then use EXECUTE.
regards, tom lane
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
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@pgedit.com> 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