pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCOPY use in function with variable file name
2+ messages / 2 participants
[nested] [flat]
* COPY use in function with variable file name
@ 2003-01-29 08:05 Sondaar Roelof <roelof.sondaar@scania.com>
0 siblings, 1 reply; 2+ messages in thread
From: Sondaar Roelof @ 2003-01-29 08:05 UTC (permalink / raw)
To: pgsql-sql
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 */
END LOOP;
RETURN 0;
END;'
LANGUAGE 'plpgsql';
Result:
id=# select dnsdhcp_dns_raw();
NOTICE: Network: /tmp/db.test.dummy.com
ERROR: parser: parse error at or near "$1"
Tables:
CREATE TABLE dns_raw (
data TEXT
);
Table "network"
Attribute | Type | Modifier
--------------------+---------+---------------------------------------------
-------
id | integer | not null default
nextval('"network_id_seq"'::text)
ipaddress | cidr | not null
domain | text | not null
email | text | not null
location | text | not null default 'l'
use | text | not null default 's'
ttl | text | not null default '3h'
serial | integer | not null default 1
refresh | text | not null default '3h'
retry | text | not null default '1h'
expire | text | not null default '1w'
cachettl | text | not null default '1d'
lease_time_default | integer | not null default 86400
lease_time_minimum | integer | not null default 0
lease_time_maximum | integer | not null default 0
client_updates | text | not null default 'ignore'
ddns_update_style | text | not null default 'interim'
description | text |
Best regards,
Roelof
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: COPY use in function with variable file name
@ 2003-02-02 21:30 Oliver Elphick <olly@lfix.co.uk>
parent: Sondaar Roelof <roelof.sondaar@scania.com>
0 siblings, 0 replies; 2+ messages in thread
From: Oliver Elphick @ 2003-02-02 21:30 UTC (permalink / raw)
To: Sondaar Roelof <roelof.sondaar@scania.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-02-02 21:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-29 08:05 COPY use in function with variable file name Sondaar Roelof <roelof.sondaar@scania.com>
2003-02-02 21:30 ` Oliver Elphick <olly@lfix.co.uk>
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