[SQL] create function
How Can I Create function delete from... with returns opague and without plpgsql language. Sample: create function fdel returns opaque as 'delete from tab;' language 'sql'; Thanks. Tomas N.
[SQL] Re: open and closed paths ...
The only problem left is the correct syntax of the command when using [].
I've tried some versions but it did not work.
Maybe Tom can include an example into the docs.
Hans
shop=# INSERT INTO temppath(fieldname) VALUES '((1,3), (4,12))';
ERROR: parser: parse error at or near "'"
shop=# INSERT INTO temppath(fieldname) VALUES ((1,3), (4,12));
ERROR: parser: parse error at or near ","
shop=# INSERT INTO temppath(fieldname) VALUES ('(1,3), (4,12)');
INSERT 51947 1
shop=# INSERT INTO temppath(fieldname) VALUES ['(1,3), (4,12)'];
ERROR: parser: parse error at or near "["
shop=# INSERT INTO temppath(fieldname) VALUES '[(1,3), (4,12)]';
ERROR: parser: parse error at or near "'"
shop=# INSERT INTO temppath(fieldname) VALUES '[(1,3), (4,12)]'::path;
ERROR: parser: parse error at or near "'"
shop=# INSERT INTO temppath(fieldname) VALUES ['(1,3), (4,12)']::path;
ERROR: parser: parse error at or near "["
shop=# SELECT isopen(fieldname) FROM temppath;
isopen
f
(1 row)
---(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] Casting numeric to text
Cedar Cox writes: > When would one want to use cast()? What is the difference between cast > and :: ? After a quick look in the documentation I couldn't find > anything.. cast() is SQL, :: is traditional Postgres. :: may go away in the distant future to make room for the SQL feature that is supposed to use ::. (It escapes me at the moment what that was.) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] please help
Cedar Cox writes: > It would be somewhat (very) useful to have something like this. We were > toying with the idea of making some sort of system to figure out if a > table is locked or not. This will probably introduce race conditions unless done very carefully. In theory you need a second level of locks to protect the information you obtained regarding the "real" locks. I'm not saying it's impossible, but 20 years ago people were writing Ph.D. theses about these sort of things. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Casting numeric to text
On Mon, Apr 09, 2001 at 05:57:45PM +0200, Peter Eisentraut wrote: > Cedar Cox writes: > > > When would one want to use cast()? What is the difference between cast > > and :: ? After a quick look in the documentation I couldn't find > > anything.. > > cast() is SQL, :: is traditional Postgres. :: may go away in the distant > future to make room for the SQL feature that is supposed to use ::. (It > escapes me at the moment what that was.) FYI, I can't find an occurance of '::' that's not part of '::=' in either SQL1992.txt or the ansi-iso-[sql]-1999.txt files I've got. But Peter's answer's still correct: CAST( AS ) is SQL standard, '::' is not. Ross ---(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] Re: open and closed paths ...
Hans-Jürgen Schönig writes:
> The only problem left is the correct syntax of the command when using [].
> I've tried some versions but it did not work.
> Maybe Tom can include an example into the docs.
>
> Hans
>
>
> shop=# INSERT INTO temppath(fieldname) VALUES '((1,3), (4,12))';
> ERROR: parser: parse error at or near "'"
Should be VALUES ('((1,3), (4,12))'); The outer parentheses belong to the
INSERT command, the quotes delimit the data literal, whatever is inside
the quotes is the data type's business.
> shop=# INSERT INTO temppath(fieldname) VALUES ((1,3), (4,12));
> ERROR: parser: parse error at or near ","
> shop=# INSERT INTO temppath(fieldname) VALUES ('(1,3), (4,12)');
> INSERT 51947 1
> shop=# INSERT INTO temppath(fieldname) VALUES ['(1,3), (4,12)'];
> ERROR: parser: parse error at or near "["
> shop=# INSERT INTO temppath(fieldname) VALUES '[(1,3), (4,12)]';
> ERROR: parser: parse error at or near "'"
> shop=# INSERT INTO temppath(fieldname) VALUES '[(1,3), (4,12)]'::path;
> ERROR: parser: parse error at or near "'"
> shop=# INSERT INTO temppath(fieldname) VALUES ['(1,3), (4,12)']::path;
> ERROR: parser: parse error at or near "["
--
Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
---(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
Re: [SQL] Casting numeric to text
Ross J. Reedstrom writes: > FYI, I can't find an occurance of '::' that's not part of '::=' in either > SQL1992.txt or the ansi-iso-[sql]-1999.txt files I've got. SQL 1999 6.12 ::= [ ] That syntax even makes sense... -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: select substr???
Hi, I have postgres 6.x (where x is something). I have the following list of data data ABC* ABC ABC- ABC+ ABC QWE~ ASD+ ASD# KLASDHK- KLASDHK+ KLASDHK KLASDHK* what I want to do is 'select distinct(data) [ignoring non alphanumeric characters] order by data' is there a way to do that? Changing the data stored in the table is not an option as the suffixes are needed elsewhere.. Please help !! Thanks, Tim. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Casting numeric to text
On Mon, Apr 09, 2001 at 06:53:13PM +0200, Peter Eisentraut wrote: > Ross J. Reedstrom writes: > > > FYI, I can't find an occurance of '::' that's not part of '::=' in either > > SQL1992.txt or the ansi-iso-[sql]-1999.txt files I've got. > > SQL 1999 6.12 > > ::= > [ ] > Ah, right: My grep excluded the ::= cases, so I missed: ::= :: > That syntax even makes sense... No, how could it be? Someone must have failed to run the Babelizer on that part. Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] pg_dump and BLOB
Hello, I am using postgreSQL 7.02 on RH Linux 6.2. I have noticed that dumping out a table containing oid (BLOBs) makes trouble. How can I managed this in order to have my BLOBs backed up correctly ? If I don't take care, I have a table with oid that don't point on any object id (relation does not exist). Thanks -- Richard NAGY Presenceweb
Re: [SQL] Re: select substr???
Tim Johnson writes: > Hi, > > I have postgres 6.x (where x is something). > > I have the following list of data > > data > > ABC* > ABC > ABC- > ABC+ > ABC > QWE~ > ASD+ > ASD# > KLASDHK- > KLASDHK+ > KLASDHK > KLASDHK* > > > what I want to do is 'select distinct(data) [ignoring non alphanumeric > characters] order by data' Write a function that strips off the suffixes and do 'select distinct voodoo(data) ...'. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Sorting and then...
Suppose I have a table create table test ( id integer, name text ); And I want to get the names of the largest 10 "id"s. How can I do that in sql? Thanks! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Inheritance Semantics
Could someone (Chris Mead?) post an update on the status of fixing PostgreSQL's inheritance semantics in the following ways: Has a decision been made to implementing true inheritance via INHERITS or an alternative keyword? By true inheritance, I mean first and foremost that any query on a super-class should query *all members* of that class by default regardless of which table they are stored in. Any other behavior violates the very natural expectation that a table called "PERSON" actually implements the class of all persons. Second, for performance reasons, there needs to be a way for an index on a parent class attribute to be shared by all the tables that implement or inherit from that parent class. This is also necessary to enforce unique constraints on all members of a class. I imagine that the current implementation of "SELECT column FROM table*" is a logical UNION ALL of the select statement applied to each sub table, using different indexes for each one - Is this correct? Third, all declarative constraints on a parent class should be enforced against all members of all sub-classes without exception. Fourth, someday it would be nice to be able to create object methods & member functions that operate in the context of a single object. Does anyone know if the OQL supports this capability? I understand the backwards compatibility issue with the current semantics. Rather than adding some sort of run-time setting, I think it would be much better to add a new keyword / extension to the DDL syntax so that true ODMG style inheritance can be implemented correctly without breaking old applications. Any comments would be appreciated. - Mark Butler ---(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] Sorting and then...
On Mon, Apr 09, 2001 at 07:22:52PM -0400, Wei Weng wrote: > And I want to get the names of the largest 10 "id"s. How can I do that in > sql? What do you mean by "largest"? Largest id? "largest" text string? If it's the id you can do: select max(id) from ; -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Backup is for whimps! ---(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
Re: [SQL] Inheritance Semantics
> Could someone (Chris Mead?) post an update on the status of fixing > PostgreSQL's inheritance semantics in the following ways: > > Has a decision been made to implementing true inheritance via INHERITS or an > alternative keyword? > > By true inheritance, I mean first and foremost that any query on a super-class > should query *all members* of that class by default regardless of which table > they are stored in. Any other behavior violates the very natural expectation > that a table called "PERSON" actually implements the class of all persons. > 7.1 does that already. > Second, for performance reasons, there needs to be a way for an index on a > parent class attribute to be shared by all the tables that implement or > inherit from that parent class. This is also necessary to enforce unique > constraints on all members of a class. That is on the TODO list, so I think we want it to happen. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
