frustrated by plpgsql procedure

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