Re: frustrated by plpgsql procedure

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(at)pgedit(dot)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