pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Test for file exists?
2+ messages / 2 participants
[nested] [flat]

* Test for file exists?
@ 2004-09-20 03:16 Richard Sydney-Smith <richard@ibisaustralia.com>
  2004-09-20 16:30 ` Re: Test for file exists? Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Richard Sydney-Smith @ 2004-09-20 03:16 UTC (permalink / raw)
  To: pgsql-sql

I want to use pgsql to send the command:

Copy sometable from 'sometable.csv';

But only if 'sometable.csv' exists;

If 'sometable.csv' does not exist as an input table I want to continue
the next command.

My full procedure is as follows and any help is greatly appreciated.

Thanks

Richard

CREATE OR REPLACE FUNCTION restore_database(text)
  RETURNS text AS
$BODY$
declare 
tblname record;
cnt record;
tname varchar :='';
tquery varchar :='';
filename varchar :='';

begin
tname := '';
for tblname in select tablename from pg_tables WHERE not(tablename like
'pg_%') and not(tablename like 't_%') 
and not(tablename like '%_list') order by tablename  loop
   raise notice '%',tblname.tablename;
   tquery := 'delete from ' || tblname.tablename ;
   execute tquery;
   filename := '/'||$1||'/'|| lower(tblname.tablename)||'.csv';
   tquery := 'copy ' || tblname.tablename || ' from ' ||
quote_literal(filename);
   execute tquery;
end loop;

return tquery;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.760 / Virus Database: 509 - Release Date: 10/09/2004
 




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Test for file exists?
  2004-09-20 03:16 Test for file exists? Richard Sydney-Smith <richard@ibisaustralia.com>
@ 2004-09-20 16:30 ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Josh Berkus @ 2004-09-20 16:30 UTC (permalink / raw)
  To: pgsql-sql

Richard,

> But only if 'sometable.csv' exists;
>
> If 'sometable.csv' does not exist as an input table I want to continue
> the next command.

Sorry, can't be done in plpgsql.   Regular procedural languages are 
deliberately kept ignorant of the host filesystem in order to prevent 
database users from being able to compromise it.   It's a security thing.

You could easily write a function in an "untrusted" language, like PL/PerlU or 
PL/PythonU, which would test for file existence and return a true/false 
value.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2004-09-20 16:30 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-20 03:16 Test for file exists? Richard Sydney-Smith <richard@ibisaustralia.com>
2004-09-20 16:30 ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox