Re: [GENERAL] Using embedded SQL.
On Sat, Jun 03, 2000 at 05:37:32PM -0500, Robert J. Sprawls wrote:
> I'm trying to learn embedded SQL in C. However, it's not working
> and reading the documentation is not answering my question(s). Here is my
Please check the test cases in the source tree. They help a little bit.
> #include
> #include
> exec sql include sqlca;
>
> int main( int argc, char *argv[] ) {
>
>exec sql whenever sqlerror sqlprint;
>exec sql connect tootle;
The correct syntax is
exec sql connect to tootle;
>exit( 0 );
>exec sql disconnect sprawlsr;
Sorry, I do not understand that line. At first your program will never
disconnect from that database since it exists before it can execute that
statement. And second you never connected to sprawlsr so how can you
dissconnect?
> }
>
> And here is what I get back:
>
> sql error Could not connect to database in line 25.
>
> What am I doing wrong? Yes, the database does exist as tootle.
Yup, that's one of those Oracle compatibility hacks. If you use connect
without 'to' ecpg expects the following term to a oracle like user
specification.
Michael
--
Michael Meskes
[EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!
Re: [GENERAL] Vacuum Question
Ed Loehr <[EMAIL PROTECTED]> writes: >> Then, start this one in another bash window/terminal/whatever... >> >> % while test 1; do echo -n "`date`: "; psql -d pdb -c "vacuum analyze; >> select count(*) from foo;"; sleep 3; done >> >> This seems to consistently crash after the first vacuum with the >> following message: This is a known gotcha that's got nothing to do with any sort of concurrency. You can't safely send a VACUUM followed by anything else in a single query string. The problem is that VACUUM forces a transaction commit, which releases all transiently allocated memory in its backend ... including the already-parsed querytrees for the rest of the query string. Oops. (cf. comment near line 560 in src/backend/tcop/postgres.c) You won't see the problem if you enter "vacuum analyze; select ..." interactively or as a script in psql, because it chops up the commands into separate query submittals. But apparently psql doesn't chop up a -c string. Non-psql frontends can expose the bug as well. It's possible that this will get cleaned up as a byproduct of the proposed rework of transaction-local memory contexts. But it's not a real high-priority problem, at least not IMHO. For now, the answer is "if it hurts, don't do it ;-)" regards, tom lane
[GENERAL] troubles installing postgresql6.4
hi, i do not know what superuser account i made. so i am not able to do initdb.. it says.. We are initializing the database system with username X (uid=500). This user will own all the files and must also own the server process. mkdir: cannot create directory `/usr/local/pgsql/data/base/template1': Relaying denied. Creating template database in /usr/local/pgsql/data/base/template1 ERROR: cannot create pg_proc ERROR: cannot create pg_proc initdb: could not create template database initdb: cleaning up by wiping out /usr/local/pgsql/data/base/template1 what should i do thanks in advance, marcos [EMAIL PROTECTED]
Re: [GENERAL] troubles installing postgresql6.4
On Tue, 6 Jun 2000, Marcos Lloret wrote: > hi, > > i do not know what superuser account i made. so i am not able to do > initdb.. it says.. > > We are initializing the database system with username X > (uid=500). > This user will own all the files and must also own the server > process. > > mkdir: cannot create directory > `/usr/local/pgsql/data/base/template1': Relaying denied. > Creating template database in /usr/local/pgsql/data/base/template1 > ERROR: cannot create pg_proc > ERROR: cannot create pg_proc > initdb: could not create template database > initdb: cleaning up by wiping out > /usr/local/pgsql/data/base/template1 > > what should i do > > thanks in advance, > > marcos > [EMAIL PROTECTED] > > Start by installing version 7.0.2 which is available at: http://www.postgresql.org then follow the installation instructions carefully and you'll be up and running in a very short time. On a decently fast machine it shouldn't take but 30-60 minutes (longer if you're on a slow internet connection). Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com ==
[GENERAL] How to backup db with large objects?
Hello, I have database with large objects? I did following: pg_dump database1 > database.out createdb database2 psql -e database2 < database.out And I can not access any large objects on database2. database2=# select lo_export(picture_immage, '/home/postgres/image2.jpg') from pictures where picture_id = 6; ERROR: Relation 390625 does not exist Do I need to make backup using some special ways? I can't find anything in docs. Any suggestions. Sincerely, Ignas
[GENERAL] Precision of calculated numeric fields
Consider the following: trbauer=# create table t1 (x numberic(3,2)); trbauer=# \d t1 Attribute | Type | Modifier X | numeric(3,2) | trbauer=# create view v1 as select x*2 from t1; trbauer=# \d v1 Attribute | Type | Modifier --- ?column? | numeric(65535,65531) | How do I get the precision on the calculated numeric field to be something sane, like 3,2? This is important for three reasons: 1.MSAccess chokes on views containing these fields (citing the precision size). 2. The jdbc driver takes _forever_ to retrieve these fields into big decimal. 3. I really don't want to reconfigure my database to use floating points :) Thanks, Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
Re: [GENERAL] Vacuum Question
Tom Lane wrote: > > Ed Loehr <[EMAIL PROTECTED]> writes: > >> Then, start this one in another bash window/terminal/whatever... > >> > >> % while test 1; do echo -n "`date`: "; psql -d pdb -c "vacuum analyze; > >> select count(*) from foo;"; sleep 3; done > >> > >> This seems to consistently crash after the first vacuum with the > >> following message: > > This is a known gotcha that's got nothing to do with any sort of > concurrency. You can't safely send a VACUUM followed by anything > else in a single query string. Well, I thought that select count(*) might've been causing a problem, so I experimented without it and found the same problem. Doesn't seem to happen with 'vacuum'or 'vacuum analyze foo', only 'vacuum analyze'... Regards, Ed Loehr
Re: [GENERAL] Errors attempting to insert duplicate values
Bryan White wrote: > > I have a table with a unique index. When I go to insert a new value into > the table my code does not know if the record is already there. To me there > are two choices: > > 1) Do a select for the record and insert only if not found. > > 2) Do a blind insert and relay on the unique index to fail the insert if > there is a duplicate. 3) Use a sequence to generate unique indicies for you? -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://cupid.suninternet.com/~kleptog/
Re: [GENERAL] 32KB Tuples
What exactly is a "tuple"? One entire row of a table? Or one "line" of a reply to a query? (I suspect it's the entire row of a table, but I have had a case with text fields, where one field was updated with about 7k. All selects would deal either with that field or the others. The first time the 8k limit struck was with with a pg_dump/restore were obviously the entire row was used, so maybe it's just the size of the reply?) Cheers, Patrick
Re: [GENERAL] How to backup db with large objects?
On Tue, 06 Jun 2000, Ignas Saltis wrote: > Hello, > > I have database with large objects? > > I did following: > > pg_dump database1 > database.out > createdb database2 > psql -e database2 < database.out > > And I can not access any large objects on database2. > > > database2=# select lo_export(picture_immage, '/home/postgres/image2.jpg') > from pictures where picture_id = 6; > ERROR: Relation 390625 does not exist > > > > Do I need to make backup using some special ways? > I can't find anything in docs. > > Any suggestions. pg_dump/all do not dump the large objects (AFAIK). ftp://ftp2.zf.jcu.cz/users/zakkr/pg/pg_dumplo-0.0.4.tar.gz might help you -- Robert B. Easter [EMAIL PROTECTED]
Re: [GENERAL] Precision of calculated numeric fields
Travis Bauer <[EMAIL PROTECTED]> writes: > Consider the following: > trbauer=# create table t1 (x numberic(3,2)); > trbauer=# \d t1 > Attribute | Type | Modifier > > X | numeric(3,2) | > trbauer=# create view v1 as select x*2 from t1; > trbauer=# \d v1 > Attribute | Type | Modifier > --- > ?column? | numeric(65535,65531) | > How do I get the precision on the calculated numeric field to be something > sane, like 3,2? You don't --- there isn't any way to specify the types of view columns. The view is being created with typmod -1 for the numeric column, which is correct behavior IMHO. The bug here is in psql: it should be showing the column type as plain "numeric", no decoration. > This is important for three reasons: 1.MSAccess chokes on views > containing these fields (citing the precision size). 2. The jdbc driver > takes _forever_ to retrieve these fields into big decimal. Sounds like the jdbc driver also gets confused when it sees typmod -1 for a numeric field. As a short-term workaround you could manually set pg_attribute's atttypmod column for the view's field. Use the same value you find in atttypmod for the underlying table's field. regards, tom lane
[GENERAL] TRIGGER Syntax
What does 'STATEMENT' refer to in the following description for CREATE
TRIGGER?
CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
ON table FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE func ( arguments )
Ron Peterson
[EMAIL PROTECTED]
Re: [GENERAL] How to backup db with large objects?
On Tue, 6 Jun 2000, Robert B. Easter wrote: > pg_dump/all do not dump the large objects (AFAIK). > > ftp://ftp2.zf.jcu.cz/users/zakkr/pg/pg_dumplo-0.0.4.tar.gz > might help you Nice. Not announced, but users know it :-) Test it anyone with 7.0? Karel /* * Karel Zak * [EMAIL PROTECTED] * http://home.zf.jcu.cz/~zakkr/ * C, PostgreSQL, PHP, WWW, http://docs.linux.cz * */
Re: [GENERAL] Vacuum Question
Ed Loehr <[EMAIL PROTECTED]> writes: > % while test 1; do echo -n "`date`: "; psql -d pdb -c "vacuum analyze; > select count(*) from foo;"; sleep 3; done > > This seems to consistently crash after the first vacuum with the > following message: >> >> This is a known gotcha that's got nothing to do with any sort of >> concurrency. You can't safely send a VACUUM followed by anything >> else in a single query string. > Well, I thought that select count(*) might've been causing a problem, so > I experimented without it and found the same problem. Doesn't seem to > happen with 'vacuum'or 'vacuum analyze foo', only 'vacuum analyze'... I can't reproduce any problem with just a "vacuum" (with or without analyze) and no following command. I did, however, notice that very occasionally the inserting process would spit out weird error messages like "Function '(int4)' does not exist" and "init_fcache: null probin for procedure 481". This seems to be due to VACUUM (on system tables) causing syscache entries to be flushed at unexpected times. I've committed patches for the two cases I observed, but there may be more lurking... regards, tom lane
Re: [GENERAL] Precision of calculated numeric fields
Is this the mod field of the Field class in JDBC2? Does -1 alway mean, for numeric types, that the precision is undefined? If so, I could patch up the ResultSet class to function properly. I traced through the ResultSet code this morning. Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer On Tue, 6 Jun 2000, Tom Lane wrote: > > Sounds like the jdbc driver also gets confused when it sees typmod -1 > for a numeric field. >
Re: [GENERAL] Vacuum Question
Tom Lane wrote: > > Ed Loehr <[EMAIL PROTECTED]> writes: > > % while test 1; do echo -n "`date`: "; psql -d pdb -c "vacuum analyze; > > select count(*) from foo;"; sleep 3; done > > > > This seems to consistently crash after the first vacuum with the > > following message: > >> > >> This is a known gotcha that's got nothing to do with any sort of > >> concurrency. You can't safely send a VACUUM followed by anything > >> else in a single query string. > > > Well, I thought that select count(*) might've been causing a problem, so > > I experimented without it and found the same problem. Doesn't seem to > > happen with 'vacuum'or 'vacuum analyze foo', only 'vacuum analyze'... > > I can't reproduce any problem with just a "vacuum" (with or without > analyze) and no following command. > > I did, however, notice that very occasionally the inserting process > would spit out weird error messages like "Function '(int4)' does not > exist" and null probin for procedure 481". This seems > to be due to VACUUM (on system tables) causing syscache entries to be > flushed at unexpected times. I've committed patches for the two cases > I observed, but there may be more lurking... Yes, I was getting a similar Function error message, though I never saw the init_fcache message. And it appeared the backend would crash coincidentally with the function error message. If the patch can be applied to 7.0 (beta3), and you don't mind posting it, I could test it here... Regards, Ed Loehr
[GENERAL] 'BETWEEN' documentation pointer?
Can anyone point me to any online documentation on how to use the 'BETWEEN' operator/function? (As in "SELECT * FROM foo WHERE id BETWEEN ...") Regards, Ed Loehr
Re: [GENERAL] Releases: 7.0.1, 7.0.2
On Tue, 6 Jun 2000, Eugene Karpachov wrote: > Excuse me, but why postgresql team doesn't release patches? I mean something > like postgresql-7.0-7.0.1.diff.gz etc. It is big waste of traffic to download > all the source distribution again. we've tried in the past, and so far nobody has been able to give us a patch command that works effectively *shrug* each time we've tried, the patch has blown up in someone's face :(
[GENERAL] Running PostgreSQL 7.0 on WinNT w/cygwinb20 and cygwin-ipc
I keep seeing the following whenever there is a new client connection to the database: Error semaphore semaphore not equal 0 anyone know what this means? Thanks. - Greg
[GENERAL] Dump
I wanted to back up a database prior to upgrading to 7.0.2 pg_dump dbname > dbname.bak FATAL 1: Memory exhausted in AllocSetAlloc() PQendcopy: resetting connection SQL query to dump the contents of Table 'tblname' did not execute correctly. After we read all the table contents from the backend, PQendcopy() failed. Explanation from backend: ''. The query was : 'COPY "tblname" TO stdout; '. Please help. Thanks, Mihai
[GENERAL] getting inet out of char?
Hi, Since everyone here has been so helpful in the past (even when I recently overlooked something obvious in The Fine Material), I wonder if I might get a pointer about what to do in this case. I have a table mapping names to ips; the access to users is through PHP3. Now, PHP's module to PostgreSQL does not know about the data type inet. I could just forget about it, but it seems to me the inet data type offers a number of advantages for easy data extraction. So, I thought the answer would be simple: I created two tables. Table "name_and_ip" Attribute |Type | Modifier ---+-+-- name | varchar(30) | not null ip| char(15)| not null Indices: name_and_ip_ip_key, name_and_ip_name_key Table "name_and_ip_v4" Attribute |Type | Modifier ---+-+-- name | varchar(30) | not null ip| inet| not null Indices: name_and_ip_v4_ip_key, name_and_ip_v4_name_key And I thought to have a rule: create rule name_ip_update as on update to name_and_ip do insert into name_and_ip_v4(name,ip) values (new.name, new.ip::inet); ERROR: Cannot cast type 'bpchar' to 'inet' Is there something else I can do? (Having followed the recent discussion on rules and triggers, I thought a rule was what I wanted.) Thanks, A -- Andrew Sullivan Computer Services <[EMAIL PROTECTED]>Burlington Public Library +1 905 639 3611 x158 2331 New Street Burlington, Ontario, Canada L7R 1J4
Re: [GENERAL] Precision of calculated numeric fields
Travis Bauer <[EMAIL PROTECTED]> writes: > Is this the mod field of the Field class in JDBC2? Sounds right, but I haven't looked at the JDBC code. > Does -1 alway mean, for > numeric types, that the precision is undefined? Right. In general, -1 is the default typmod if Postgres doesn't have any better idea what to put in. So type-specific code always has to be prepared to do something reasonable with that typmod value ... regards, tom lane
Re: [GENERAL] Vacuum Question
Ed Loehr <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> I can't reproduce any problem with just a "vacuum" (with or without
>> analyze) and no following command.
>>
>> I did, however, notice that very occasionally the inserting process
>> would spit out weird error messages like "Function '(int4)' does not
>> exist" and null probin for procedure 481". This seems
>> to be due to VACUUM (on system tables) causing syscache entries to be
>> flushed at unexpected times. I've committed patches for the two cases
>> I observed, but there may be more lurking...
> Yes, I was getting a similar Function error message, though I never saw
> the init_fcache message. And it appeared the backend would crash
> coincidentally with the function error message. If the patch can be
> applied to 7.0 (beta3), and you don't mind posting it, I could test it
> here...
Hmm, I only saw error messages, no crashes --- but I suppose a crash is
possible, since the root of the problem here is a dangling pointer.
Patches for 7.0.2 are attached. Not sure if they will apply perfectly
cleanly to beta3, but you should be able to make the right mods by hand
if patch doesn't cope...
regards, tom lane
*** src/backend/parser/parse_type.c.origTue May 30 00:24:49 2000
--- src/backend/parser/parse_type.c Tue Jun 6 11:41:08 2000
***
*** 48,54
return NULL;
}
typetuple = (Form_pg_type) GETSTRUCT(tup);
! return NameStr(typetuple->typname);
}
/* return a Type structure, given a type id */
--- 48,55
return NULL;
}
typetuple = (Form_pg_type) GETSTRUCT(tup);
! /* pstrdup here because result may need to outlive the syscache entry */
! return pstrdup(NameStr(typetuple->typname));
}
/* return a Type structure, given a type id */
***
*** 119,125
Form_pg_type typ;
typ = (Form_pg_type) GETSTRUCT(t);
! return NameStr(typ->typname);
}
/* given a type, return its typetype ('c' for 'c'atalog types) */
--- 120,127
Form_pg_type typ;
typ = (Form_pg_type) GETSTRUCT(t);
! /* pstrdup here because result may need to outlive the syscache entry */
! return pstrdup(NameStr(typ->typname));
}
/* given a type, return its typetype ('c' for 'c'atalog types) */
*** src/backend/utils/cache/fcache.c~ Wed Apr 12 13:15:53 2000
--- src/backend/utils/cache/fcache.cTue Jun 6 13:39:03 2000
***
*** 14,19
--- 14,20
*/
#include "postgres.h"
+ #include "access/heapam.h"
#include "catalog/pg_language.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
***
*** 89,97
if (!use_syscache)
elog(ERROR, "what the , init the fcache without the catalogs?");
! procedureTuple = SearchSysCacheTuple(PROCOID,
!
ObjectIdGetDatum(foid),
!0, 0,
0);
if (!HeapTupleIsValid(procedureTuple))
elog(ERROR, "init_fcache: Cache lookup failed for procedure %u",
--- 90,98
if (!use_syscache)
elog(ERROR, "what the , init the fcache without the catalogs?");
! procedureTuple = SearchSysCacheTupleCopy(PROCOID,
!
ObjectIdGetDatum(foid),
!
0, 0, 0);
if (!HeapTupleIsValid(procedureTuple))
elog(ERROR, "init_fcache: Cache lookup failed for procedure %u",
***
*** 258,263
--- 259,266
}
else
retval->func.fn_addr = (func_ptr) NULL;
+
+ heap_freetuple(procedureTuple);
return retval;
}
[GENERAL] deferred primary key
Fellow postgresers:
According to the web site the following syntax is permitted:
PRIMARY KEY clause
SQL92 specifies some additional capabilities for PRIMARY KEY:
Table Constraint definition:
[ CONSTRAINT name ] PRIMARY KEY ( column [, ...] )
[ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
[ [ NOT ] DEFERRABLE ]
My snytax is:
constraint base_pos_pkey primary key (base, pos)
initially deferred
Postgres complained about the "initially"
Are deferrable primary keys not supported by Postgres?
Thanks!
Merrill
[GENERAL] Composite Types
Hi, I appreciate any help I can get on this...Recently, I've been experimenting with the user defined base-types (using CREATE TYPE) with successful results. But, when it comes to handling classes as composite types, things are not as straight forward. Here is a case. Suppose I define a composite type (table) called 'bulk': Table "bulk" Attribute | Type | Modifier ---+-+-- id| integer | ofset | integer | size | integer | and use the composite type 'bulk' in another table called 'stuff' as in: Table "stuff" Attribute | Type | Modifier ---+-+-- id| integer | name | text| content | bulk| My intent here is to maintain consistency between the instances of these tables (i.e., for each instance of 'bulk', there would an equivalent instance in the 'content' of 'stuff'. My first question is what is the best way to insert into 'stuff'? I couldn't come up with a simple solution other than creating the function new_bulk(): CREATE function new_bulk(int4, int4, int4) returns bulk as ' select $1, $2, $3 ' LANGUAGE 'sql'; and using it to insert a row in 'stuff': INSERT into stuff values (1,'test1',(new_bulk(7,8,9))); INSERT into bulk values (7,8,9); The 2nd question I have is how to come up with an output function (similar to output_function in CREATE TYPE) to display 'content' of 'stuff' in a suitable format? If I type: select content from stuff; I get: content --- 136585664 (1 row) and select stuff.content.id from content; gives me: ERROR: init_fcache: Cache lookup failed for procedure 136585664 Any suggestions/help are appreciated. Morey Parang Oak Ridge National Laboratory [EMAIL PROTECTED]
Re: [GENERAL] Dump
Mihai Gheorghiu <[EMAIL PROTECTED]> writes: > I wanted to back up a database prior to upgrading to 7.0.2 > pg_dump dbname > dbname.bak > FATAL 1: Memory exhausted in AllocSetAlloc() > PQendcopy: resetting connection > SQL query to dump the contents of Table 'tblname' did not execute correctly. Hmm, what version are you using now? COPY used to have a memory leak problem according to the CVS logs, but that was long ago (pre-6.4). regards, tom lane
Re: [GENERAL] index problem
At 10:53 AM 05-06-2000 +0200, Marcin Inkielman wrote: >On Mon, 5 Jun 2000, Lincoln Yeoh wrote: >I solved my problem simplier: > >drop index oceny_stud_numer_albumu_protokol_id; > >and > >drop index oceny_stud_numer_albumu_protokoloceny_stud; > >failed > > >so I used: > >drop index "oceny_stud_numer_albumu_protokoloceny_stud"; >and it worked for me 8-))) I wonder why it worked tho. How does Postgresql treat stuff between double quotes, especially regard to string length limits? Yes I know it's in the source code somewhere, but it's also good to know the official/intended behaviour vs the actual behaviour. Cheerio, Link.
[GENERAL] Are the subscription CDs version 7
The pictures of the subscription CDs still show version 6.5. If I ordered a subscritpion today, what version whould I get on CD? ** The information contained in this E-Mail is confidential and is intended only for the use of the addressee(s). If you receive this E-Mail in error, any use, distribution or copying of this E-Mail is not permitted. You are requested to forward unwanted E-Mail and address any problems to the MIM Holdings Limited Support Centre. E-Mail: [EMAIL PROTECTED] or phone: Australia 07 3833 8042. **
Re: [GENERAL] Are the subscription CDs version 7
They are definitely v7.0 ... I've CC'd Jeff in to see about getting that
image updated ... thanks for the poiner ...
On Wed, 7 Jun 2000, George Dau wrote:
> The pictures of the subscription CDs still show version 6.5. If I ordered a
> subscritpion today, what version whould I get on CD?
>
>
> **
> The information contained in this E-Mail is confidential
> and is intended only for the use of the addressee(s).
> If you receive this E-Mail in error, any use, distribution
> or copying of this E-Mail is not permitted. You are
> requested to forward unwanted E-Mail and address any problems
> to the MIM Holdings Limited Support Centre.
> E-Mail: [EMAIL PROTECTED] or phone: Australia 07 3833 8042.
> **
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [GENERAL] Composite Types
[EMAIL PROTECTED] writes: > I appreciate any help I can get on this...Recently, I've been experimenting > with the user defined base-types (using CREATE TYPE) with successful results. > But, when it comes to handling classes as composite types, things are not > as straight forward. The function-returning-composite-type feature is something we inherited from Berkeley Postgres it doesn't fit into SQL92 at all, and I don't think any of the current crop of developers even understand it very well. It's certainly suffering from bit-rot. The "hobbies" examples in the regression tests seem to be meant to illustrate how it was supposed to work, but I don't find them either intelligible or persuasive. > If I type: > select content from stuff; > I get: > content > --- > 136585664 > (1 row) I believe you are looking at a numeric equivalent of a pointer-to- TupleTableSlot there. Somewhere in the mists of Berkelian prehistory, there must have been some code that did something useful with that kind of function result, but I sure as heck can't find much trace of it now. I have been thinking lately that functions returning tuples might fit into SQL92 better as table sources. That is, instead of select foo(bar).whatdoyouwritehere ... we could write something like select elementa,elementc+1 from foo(bar) That doesn't work at the moment, of course, but it's something we could think about causing to work as part of the querytree redesign planned for 7.2. Thoughts anyone? regards, tom lane
Re: [GENERAL] getting inet out of char?
Andrew Sullivan <[EMAIL PROTECTED]> writes: > I have a table mapping names to ips; the access to users is through PHP3. > Now, PHP's module to PostgreSQL does not know about the data type inet. I > could just forget about it, but it seems to me the inet data type offers a > number of advantages for easy data extraction. Seems like the cleanest answer would be to teach PHP about inet data type (not to mention other extension types). I've got no idea what that would take, but ... > create rule name_ip_update as on update to name_and_ip do insert into > name_and_ip_v4(name,ip) values (new.name, new.ip::inet); > ERROR: Cannot cast type 'bpchar' to 'inet' > Is there something else I can do? (Having followed the recent discussion on > rules and triggers, I thought a rule was what I wanted.) You could create a C-coded function to do the transformation, or you could replace the rule with a trigger coded in pltcl or plperl. I believe both the pltcl and plperl languages are basically datatype- ignorant --- as long as the textual output from one data value looks like the format the input routine for another type is expecting, it'll work. SQL rules and plpgsql functions are far more anal- retentive about type checking. Sometimes that's good, sometimes not so good. regards, tom lane
Re: [GENERAL] Releases: 7.0.1, 7.0.2
Tue, Jun 06, 2000 at 03:24:56PM -0300, The Hermit Hacker ÐÉÛÅÔ: > On Tue, 6 Jun 2000, Eugene Karpachov wrote: > > > Excuse me, but why postgresql team doesn't release patches? I mean something > > like postgresql-7.0-7.0.1.diff.gz etc. It is big waste of traffic to download > > all the source distribution again. > > we've tried in the past, and so far nobody has been able to give us a > patch command that works effectively *shrug* each time we've tried, the > patch has blown up in someone's face :( Ok, I'll try it myself. I have no 7.0.1 so I trying to make diff from 7.0 to 7.0.2. %tar -zxf postgresql-7.0.tar.gz %tar -zxf postgresql-7.0.2.tar.gz %diff -rNU 3 postgresql-7.0 postgresql-7.0.2 > postgresql-7.0-7.0.2.diff %wc postgresql-7.0-7.0.2.diff 23411 103967 914476 postgresql-7.0-7.0.2.diff It has not blown up in my face! 914476 bytes only. And better: %diff -rNU 3 postgresql-7.0 postgresql-7.0.2 | gzip -9 >postgresql-7.0-7.0.2.diff.gz %wc -c postgresql-7.0-7.0.2.diff.gz 163903 postgresql-7.0-7.0.2.diff.gz or better: %diff -rNU 3 postgresql-7.0 postgresql-7.0.2 | bzip2 >postgresql-7.0-7.0.2.diff.bz2 %wc -c postgresql-7.0-7.0.2.diff.gz 131978 postgresql-7.0-7.0.2.diff.bz2 131978 bytes - it is not the same as 7211311 (postgresql-7.0.2.tar.gz)! The only problem is how to deal with binary files - those .gz etc? May be include them into distribution untarred/ungzipped because the whole distribution is gzipped anyway? -- jk
