Forum OpenACS Q&A: aolserver scheduled event

Collapse
1: aolserver scheduled event
Posted by Paul Owen on 10/09/03 04:36 PM
Hello,

I would like to schedule a database update from a txt file every mid-night, and i have put the following into my package-procs.tcl

ad_proc -public student_upload {} { } { db_1row dummy "select file__upload()" }

ns_schedule_daily -thread 0 0 student_upload

is this correct? i didn't get the update i expected. Also, is there any tips on how to do debuging with scheduled events? i did it by keep setting the time to the next minute of the system...hmm

Also in my plpgsql function to update, i woudl like to input a filename as an argument, create or replace function test__upload(text) returns integer as ' declare p_filename alias for $1; begin copy mydb from p_filename USING DELIMITERS ''|''; return 0; end;' language 'plpgsql'; and i got the following.

ERROR: parser: parse error at or near "$1" at character 27

it works when i type the actual filename into the copy function. but how to make it an argument

Thanks

Paul

Collapse
2: Re: aolserver scheduled event (response to 1)
Posted by Tom Ayles on 10/09/03 05:19 PM

I'm afraid I can't help with the first part of your problem, but you can solve the problem with your PL/PGSQL function by changing the type of the argument from text to varchar, so your definition becomes: 

create or replace function test__upload(varchar) returns integer
as 'declare
  p_filename alias for $1;
begin
  copy mydb from p_filename USING DELIMITERS ''|'';
  return 0;
end;' language 'plpgsql';

I'm not fully sure why this is solves the problem, but it does! text is a bit of a PostgreSQL special type, which might have something to do with it. Any insight into this appreciated :). I shouldn't think that the character limit on a varchar will bother you with a file name (I think the limit is 1024 chars in PostgreSQL, but it might be more).

Collapse
3: Re: aolserver scheduled event (response to 1)
Posted by Paul Owen on 10/09/03 05:42 PM
Thanks for the reply, but actually I have tried using varchar at the beginning, and it also doens't work, and  i tried it again just now in case any typo before, the same error as above pop up...any hints? as i said the function work perfectly if i type in the actual file path in the function instead of using the parameter, so i am pretty sure it is something to do with the type of the input
Collapse
4: Re: aolserver scheduled event (response to 1)
Posted by Tom Ayles on 10/09/03 06:04 PM

Many apologies! I thought that this definition worked, but on trying it again later, it didn't - I must have had a different definition (probably using a constant instead of the var in the copy statement) lying around in PSQL. However, as penance for this flagrant disinformation, I have found a solution. It's not pretty tho! 

CREATE OR REPLACE FUNCTION test__upload(varchar) RETURNS integer
AS '
DECLARE
  p_filename ALIAS FOR $1;
BEGIN
  EXECUTE ''COPY mydb FROM ''''''
    || p_filename
    || '''''' DELIMITERS ''''|'''' ''; 
  RETURN 0;
END;' LANGUAGE 'plpgsql';

Nice... Anyway, I think the root of your problem was that COPY will only accept a literal string for the filename and not a variable. The above nastiness creates the appropriate COPY command by interpolating the variable into the right position and executes it uses PL/PGSQLs dynamic query interpretation. Be extra careful with the quotes - PL/PGSQL gets a bit hairy when you want to quote quoted quotes...

I hope this works for you, I tested it on PostgreSQL 7.2.4. Apologies again for the earlier mistake!

PS - the above definition works for text as well as varchar, I think.

Collapse
5: Re: aolserver scheduled event (response to 1)
Posted by Paul Owen on 10/09/03 06:31 PM
nice, thanks for solving the problem