[HACKERS] why the DB file size does not reduce when 'delete' the data in DB?
I had deleted a very large number of records out of my SQL table in order to decrease the harddisk space. But after I use command 'ls -l /usr/local/pgsql/data/base/', it is found that the size of concerning files do not reduce due to the effect of 'delete' SQL command. What should I do if I would like to decrease the harddisk space? I am looking forward to your response. Thank you very much for any help. - Jaruwan
[SQL] Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?
On Fri, 2 Mar 2001, Jaruwan Laongmal wrote: > I had deleted a very large number of records out of my SQL table in order to > decrease the harddisk space. But after I use command 'ls -l > /usr/local/pgsql/data/base/', it is found that the size of concerning files > do not reduce due to the effect of 'delete' SQL command. What should I do > if I would like to decrease the harddisk space? VACUUM
[SQL] Help creating rules/triggers/functions
Hi all ! I use PostgreSQL 7.0.2 on a HP-UX system. I would like to create a simple function and a simple trigger (or rule) that deny a delete from a table if the row is referenced in another table. I though it should look like this (from my Ingres experience... :) : create function A_del(int4 i_id) BEGIN SELECT id FROM b where a_id = :i_id; if rowcount > 0 then RAISE EXCEPTION "not allowed !" end if; END create trigger before delete from A for each row execute procedure A_del(old.id) But it seems to be much more complicated with Postgres (create a C function using CurrentTriggerData,...). May I have missed something or is it really much more complicated ? Thanks for help. _ B. Carrupt ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] create function w/indeterminate number of args?
In case anyone else was interested in this issue: I hadn't fully
understood the power of the fact that min(int4,int4) was a different
function from min(int4,int4,int4). It's not exactly an implementation
of an indeterminate number of arguments, but I used the feature to make
min() work for any number of arguments up to 6. The method is obviously
extensible further, but 6 is all I need for the moment. It's still ugly,
but maybe slightly less so than Ansley's kind solution. Here's the SQL
code:
CREATE FUNCTION min(int4, int4)
RETURNS int4
AS 'BEGIN
IF $1 > $2
THEN
RETURN $2;
ELSE
RETURN $1;
END IF;
END;'
LANGUAGE 'plpgsql';
CREATE FUNCTION min(int4,int4,int4)
RETURNS int4
AS 'BEGIN
RETURN min($1, min($2, $3));
END;'
LANGUAGE 'plpgsql';
CREATE FUNCTION min(int4,int4,int4,int4)
RETURNS int4
AS 'BEGIN
RETURN min(min($1,$2),min($3,$4));
END;'
LANGUAGE 'plpgsql';
CREATE FUNCTION min(int4,int4,int4,int4,int4)
RETURNS int4
AS 'BEGIN
RETURN min($1,min($2,$3),min($4,$5));
END;'
LANGUAGE 'plpgsql';
CREATE FUNCTION min(int4,int4,int4,int4,int4,int4)
RETURNS int4
AS 'BEGIN
RETURN min(min($1,$2),min($3,$4),min($5,$6));
END;'
LANGUAGE 'plpgsql';
> Michael Ansley wrote:
>
> Really ugly, but you could cast to string and concatenate with commas:
>
> minimum(arg1 || ',' || arg2 || ',' || arg3 || ',' || arg4 || ',' ||
> arg5)
>
> i.e.:
>
> create function minimum(text) returns integer
>
> and then do the parsing internally ('specially if you're using perl).
> Pretty bad, but it's an option ;-)
>
> -Original Message-
> From: Andrew Perrin [mailto:[EMAIL PROTECTED]]
> Sent: 26 February 2001 05:05
> To: [EMAIL PROTECTED]
> Subject: [SQL] create function w/indeterminate number of args?
>
> Greetings.
>
> I find myself in need of a minimum() function. This is different from
> the
> min() function, which operates across records; what I need is to be
> able
> to say:
>
> UPDATE foo SET newcol = minimum(field1, field2, field3, field4);
>
> From what I can tell there is no such beast built in, but I would be
> happy
> to be proved wrong.
>
> Anyway... I can write such a function easily enough in perl, probably
> something like:
>
> my $min=$_[0];
> $min > $_ and $min = $_ foreach @_;
> return $min;
>
> but I can't determine how to allow varying numbers of arguments to be
> passed to a function. That is, sometimes I need minimum(arg1, arg2)
> but
> sometimes it's minimum(arg1, arg2, arg3, arg4, arg5), etc.
>
> Thanks-
> Andy Perrin
>
> --
>
> Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
> Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
> [EMAIL PROTECTED] - [EMAIL PROTECTED]
>
> **
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> Nick West - Global Infrastructure Manager.
>
> This footnote also confirms that this email message has been swept by
> MIMEsweeper for the presence of computer viruses.
>
> www.mimesweeper.com
> **
--
--
Andrew J. Perrin - Programmer/Analyst, Desktop Support
Children's Primary Care Research Group, UNC - Chapel Hill
(919)966-9394 * [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Help needed -> ERROR: record arow has no field description
Hi all, I'm getting this error, which to me makes no sense. Running PostgreSQL 7.0.3 on Mandrake 7.2 (compiled from source, not the rpms). The code is in a pl/pgsql function I am writing and I can't see why it's complaining. This is the appropriate part of the code : arowrecord; BEGIN FOR arow IN select transaction_payments.cashback from transaction_payments, payment_types where transaction_payments.payment_type = payment_types.idnum LOOP textbuf := text(arow.cashback); textbuf := textcat(textbuf, '' ''); insert into receipts (receipt_num, data) values (trans_num, textbuf); END LOOP; >From the PostgreSQL log file (debug is set to 2), I am getting : query: SELECT transaction_payments.cashback from transaction_payments, payment_types where transaction_payments.payment_type = payment_types.idnum ERROR: record arow has no field description DEBUG: Last error occured while executing PL/pgSQL function create_receiptp3 DEBUG: line 105 at assignment AbortCurrentTransaction The "arow" record type variable is used quite a lot in previous FOR loops in this function. This is the first FOR loop in the function that uses more than one table though. I suspect this may have something to do with it. As far as I can tell, this SQL statement is valid. Does anyone have any ideas how to get this to work. I have tried several variations, and the mailing lists don't even have a reference for this error message. Regards and best wishes, Justin Clift Database Administrator ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Insert into VIEW ???
Is it possible to INSERT into xyz where xyz is a view ? What is the proper syntax ? Thanks Much Jacek Zagorski Shasta NetWorks LLC [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] SQL copy from csv with explicit field ordering
I guess this is really a SQL question: I have a csv that I want to import, but the csv has different column ordering. I have tried putting the column names in the first row, but then the copy command fails on field which is data type (eg it is seeing the cells in first row as data, not header info). ggtest=> copy ap_trans from '/var/dbase/ap_trans.csv' using delimiters ','; ERROR: pg_atoi: error in "id": can't parse "id" The help indicates: ggtest=> \h copy Command: copy Description: copy data to and from a table Syntax: COPY [BINARY] class_name [WITH OIDS] TO|FROM filename|STDIN|STDOUT [USING DELIMITERS 'delim']; I have tried WITH OIDS but with same results. Is there somewhere that I can either enable the first line of CSV as header names OR Can I explicitly define my import field ordering from within the select statement? Thanks Terry Fielder [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] why the DB file size does not reduce when 'delete' the data in DB?
> I had deleted a very large number of records out of my SQL table in order to > decrease the harddisk space. But after I use command 'ls -l > /usr/local/pgsql/data/base/', it is found that the size of concerning files > do not reduce due to the effect of 'delete' SQL command. What should I do > if I would like to decrease the harddisk space? Run "vacuum" from SQL or "vacuumdb" from the command line. Tables will be reduced in size, though currently indices are not. - Thomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] lo_import for storing Blobs
I need to store a binary file in a database. I use a cgi writed in shell to do it. So I can use Postgres user to execute the cgi. How can I store a binary file in a database with a cgi ? Thanks a lot. Laurent. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] union & subqueries
hello, when I run following sql in 2 separate queries, they work fine. but when used as one beg statement with UNION, I get following error: join_references: variable not in subplan target lists If I remove subqueries, they also work fine. I'm currently using postgres 7.0.2. would upgrading to 7.0.3 or 7.1 beta fix this? or is there a workaround for current version? Query is supposed to get all AP/AR transactions with related customer/vendor records and transactions expense/income account name (with subquery). 8< 8< SELECT ar.id, ar.invnumber as source, ar.notes, customer.name, customer.addr1, customer.addr2, customer.addr3, acc_trans.transdate, ar.invoice, acc_trans.amount, ( SELECT C.description FROM chart C, acc_trans AT WHERE AT.trans_id = ar.id AND AT.amount > 0 AND AT.amount=-ar.amount AND C.accno=AT.accno ) as desc, 'ar' as type FROM ar, acc_trans WHERE acc_trans.accno IN (1061,1065) AND acc_trans.trans_id = ar.id AND ar.customer = customer.id UNION SELECT ap.id, ap.ordnumber as source, ap.notes, vendor.name, vendor.addr1, vendor.addr2, vendor.addr3, acc_trans.transdate, ap.invoice, acc_trans.amount, ( SELECT C.description FROM chart C, acc_trans AT WHERE AT.trans_id = ap.id AND AT.amount < 0 AND AT.amount=-ap.amount AND C.accno=AT.accno ) as desc, 'ap' as type FROM ap, acc_trans WHERE acc_trans.accno IN (1061,1065) AND acc_trans.trans_id = ap.id AND ap.vendor = vendor.id ORDER BY transdate 8< 8< -- Martin Lillepuu | E-mail: [EMAIL PROTECTED] | GSM: 051 56 450 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Re: dates in functions
The correct function is:
CREATE function anyo_hidro (date) returns int AS '
BEGIN
RETURN date_part(''month'',$1);
END;
' LANGUAGE 'plpgsql';
By the way: Do you know what 'RTFM' means?
Salvador Mainé escribió:
>
> Hello:
>
> I'm trying to define a function that, given a date, returns its month.
> The definition is as follows:
>
> CREATE function anyo_hidro (date) returns int AS '
>BEGIN
>RETURN date_part("month",$1);
>END;
> ' LANGUAGE 'plpgsql';
>
> But when I do:
>
> select anyo_hidro('1-1-1999');
>
> I get the following error:
>
> ERROR: Attribute 'month' not found
>
> Why doesn't date_part work with pl/sql functions?
>
> Thanks
>
> --
> Salvador Maine
> http://www.ronincoders.com
--
Salva
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] dates in functions
Hello:
I'm trying to define a function that, given a date, returns its month.
The definition is as follows:
CREATE function anyo_hidro (date) returns int AS '
BEGIN
RETURN date_part("month",$1);
END;
' LANGUAGE 'plpgsql';
But when I do:
select anyo_hidro('1-1-1999');
I get the following error:
ERROR: Attribute 'month' not found
Why doesn't date_part work with pl/sql functions?
Thanks
--
Salvador Maine
http://www.ronincoders.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [SQL] Help creating rules/triggers/functions
Hello Blaise,
The following is how I tested your question;
DROP SEQUENCE b_id_seq;
DROP TABLE b;
CREATE TABLE b (
id SERIAL,
description TEXT
);
INSERT INTO b (description) VALUES('a');
INSERT INTO b (description) VALUES('b');
SELECT * FROM b;
DROP FUNCTION a_del(int4);
CREATE FUNCTION a_del(int4)
RETURNS int4
AS 'DECLARE
BEGIN
IF (SELECT count(*) FROM b WHERE id = $1) > 0 THEN
RAISE EXCEPTION ''not allowed !'';
end if;
RETURN $1;
END;'
LANGUAGE 'plpgsql';
SELECT A_del(45);
SELECT A_del(1);
The answer is:
dhcp=# SELECT a_del(45);
a_del
---
45
(1 row)
dhcp=# SELECT a_del(1);
ERROR: not allowed !
I hope this helps.
Best regrards,
Roelof
> -Original Message-
> From: Blaise Carrupt [SMTP:[EMAIL PROTECTED]]
> Sent: 27 February 2001 17:43
> To: [EMAIL PROTECTED]
> Subject: [SQL] Help creating rules/triggers/functions
>
> Hi all !
>
> I use PostgreSQL 7.0.2 on a HP-UX system.
>
> I would like to create a simple function and a simple trigger (or rule)
> that
> deny a delete from a table if the row is referenced in another table.
>
> I though it should look like this (from my Ingres experience... :) :
>
> create function A_del(int4 i_id)
> BEGIN
>SELECT id
> FROM b
> where a_id = :i_id;
>
>if rowcount > 0 then
> RAISE EXCEPTION "not allowed !"
>end if;
> END
>
>
> create trigger before delete from A for each row execute procedure
> A_del(old.id)
>
>
> But it seems to be much more complicated with Postgres (create a C
> function
> using CurrentTriggerData,...). May I have missed something or is it really
> much
> more complicated ?
>
> Thanks for help.
>
> _
> B. Carrupt
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL copy from csv with explicit field ordering
Terry Fielder writes: > Is there somewhere that I can either enable the first line of CSV as > header names > > OR > > Can I explicitly define my import field ordering from within the select > statement? No and no. You will have to preprocess your file. Something like this will probably do: #! /bin/sh IFS=, cat "inputfile" | \ while read COL1 COL2 COL3; do echo "$COL3,$COL2,$COL1" >> "outputfile" done -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Help creating rules/triggers/functions
If you're only doing a simple check for reference, why not use foreign keys? In general however, you probably want to use plpgsql to define the trigger. And trigger functions don't take parameters in the normal sense, the function should be created taking no args and returning opaque; the parameters you add in create trigger are passed in TG_ARGS (i believe). You might want to look at the user and programmer guides for more information on trigger functions. On Tue, 27 Feb 2001, Blaise Carrupt wrote: > Hi all ! > > I use PostgreSQL 7.0.2 on a HP-UX system. > > I would like to create a simple function and a simple trigger (or rule) that > deny a delete from a table if the row is referenced in another table. > > I though it should look like this (from my Ingres experience... :) : > > create function A_del(int4 i_id) > BEGIN >SELECT id > FROM b > where a_id = :i_id; > >if rowcount > 0 then > RAISE EXCEPTION "not allowed !" >end if; > END > > > create trigger before delete from A for each row execute procedure A_del(old.id) > > > But it seems to be much more complicated with Postgres (create a C function > using CurrentTriggerData,...). May I have missed something or is it really much > more complicated ? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] union & subqueries
Martin Lillepuu <[EMAIL PROTECTED]> writes: > when I run following sql in 2 separate queries, they work fine. but when > used as one beg statement with UNION, I get following error: > join_references: variable not in subplan target lists AFAICT this works OK in 7.1beta. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Database Question
I have a quick question. Is there a way in ACCESS to get the table names using SQL? Mike ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] why the DB file size does not reduce when 'delete' the data in DB?
Jaruwan Laongmal wrote: > I had deleted a very large number of records out of my SQL table in order to > decrease the harddisk space. But after I use command 'ls -l > /usr/local/pgsql/data/base/', it is found that the size of concerning files > do not reduce due to the effect of 'delete' SQL command. What should I do > if I would like to decrease the harddisk space? Run the command VACUUM; This will do the actual removal of deleted records. DELETE just marks them as deleted > I am looking forward to your response. Thank you very much for any help. > - > Jaruwan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Temp Tables & Connection Pooling
I use PostgreSQL via a connection pooling mechanism, whether it be J2EE or PHP. I've been able to achieve good performance this way, and it has been good to me. Recently I wanted to implement Dijkstra's algorithm as a stored procedure, and finding that PL/PGSQL cannot return record sets, I thought about using a temporary table for the results. If tempoary tables are session-specific, however, then wouldn't connection pooling make it unusable since the table might "disappear" from one query to the next? What are alternative approaches to implementing Dijkstra's algorithm inside the database? I'd appreciate any insights. Gerald. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Temp Tables & Connection Pooling
On Fri, 2 Mar 2001, Gerald Gutierrez wrote: ->Recently I wanted to implement Dijkstra's algorithm as a stored procedure, ->and finding that PL/PGSQL cannot return record sets, I thought about using ->a temporary table for the results. If tempoary tables are session-specific, ->however, then wouldn't connection pooling make it unusable since the table ->might "disappear" from one query to the next? What are alternative ->approaches to implementing Dijkstra's algorithm inside the database? Wouldn't a VIEW do what you want? -- Dave ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Help needed -> ERROR: record arow has no field description
Justin Clift <[EMAIL PROTECTED]> writes: > I'm getting this error, which to me makes no sense. Running PostgreSQL > 7.0.3 on Mandrake 7.2 (compiled from source, not the rpms). > ERROR: record arow has no field description Hm. If you don't have any references to "arow.description" then this seems like it must be a plpgsql bug. But your report is not detailed enough to try to reproduce it. Could you provide a self-contained example? Are there any fields named "description" in any of the tables used in this function? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
