X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7A91B475425 for ; Sun, 2 Feb 2003 16:30:23 -0500 (EST) Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net [194.217.242.85]) by postgresql.org (Postfix) with ESMTP id D7B1A4753A1 for ; Sun, 2 Feb 2003 16:30:22 -0500 (EST) Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk) by anchor-post-35.mail.demon.net with esmtp (Exim 3.36 #2) id 18fRgz-0006vG-0Z; Sun, 02 Feb 2003 21:30:23 +0000 Received: from localhost ([127.0.0.1] ident=olly) by linda.lfix.co.uk with esmtp (Exim 3.36 #1 (Debian)) id 18fRgu-0002Pb-00; Sun, 02 Feb 2003 21:30:16 +0000 Subject: Re: COPY use in function with variable file name From: Oliver Elphick To: Sondaar Roelof Cc: pgsql-sql@postgresql.org In-Reply-To: References: Content-Type: text/plain Organization: LFIX Limited Message-Id: <1044221415.32731.507.camel@linda.lfix.co.uk> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 02 Feb 2003 21:30:16 +0000 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/38 X-Sequence-Number: 11436 On Wed, 2003-01-29 at 08:05, Sondaar Roelof wrote: > Hello, > > I can't figure out how to make this work, or is not possible? > > In a function i would like to read a file. > The file name is determined by a value from a table. > However the COPY statement does not to accept this? > I tried various forms of adding (single)-quotes but no luck. > > Anyone any ideas? > > Function: > CREATE FUNCTION dnsdhcp_dns_raw() > /* Fill table dns_raw with dns data */ > RETURNS integer AS ' > DECLARE > r RECORD; > ntw TEXT; > BEGIN > /* Do for all domain names */ > FOR r IN SELECT domain FROM network > WHERE position(''n'' IN use) > 0 and ipaddress != ''127.0.0.0/24'' > LOOP > ntw := ''/tmp/db.'' || r.domain; > DELETE FROM dns_raw; /* Clear table */ > RAISE NOTICE ''Network: %'', ntw; > COPY dns_raw FROM ntw DELIMITERS ''~''; /* Get the data */ Since ntw has variable content, you need to do an EXECUTE with the command in a text string: EXECUTE ''COPY dns_raw FROM '' || ntw || '' DELIMITERS ''''=''''''; (I hope that is the right number of quotes!) > END LOOP; > RETURN 0; > END;' > LANGUAGE 'plpgsql'; -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Love not the world, neither the things that are in the world. If any man love the world, the love of the Father is not in him...And the world passeth away, and the lust thereof; but he that doeth the will of God abideth for ever." I John 2:15,17