Re: [SQL] Question re: relational technique
Robert Paulsen wrote: This still requires me to modify the overall database structure but not the original item table. As my reward :) I get to use any type I choose for each new attribute. The whole point of the database structure is to accurately reflect the requirements of your data. If you don't want your change your structure to keep track of the real world, why bother to structure it in the first place? Just stick it all in text documents and let htdig free-text search against it. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Permission to Select
Hi all
the serious problem with permissions is encountered
NOTE: the following example is really useful but there is no room to
describe it's use.
db=# CREATE USER u;
db=# CREATE TABLE t (i int, a text);
db=# REVOKE all ON t FROM u;
db=# GRANT update,insert,delete ON t TO u;
db=# \c - u
db=> INSERT INTO t VALUES (1,'x');
INSERT
db=> UPDATE t SET a='y' WHERE i=1;
ERROR: Permission denied for relation t;
db=> UPDATE t SET a='y';
UPDATE
1) The user "u" is permitted but unable to perfom the operation !
2) A user is able to update WHOLE table but unable to update ANY part of
it !
Please examine the following patch and make your judgment:
--- src/backend/executor/execMain.c.orig 2005-11-22 1:23:08.0 +0300
+++ src/backend/executor/execMain.c 2006-02-17 13:19:29.0 +0300
@@ -460,6 +460,16 @@
booldo_select_into;
TupleDesc tupType;
+ if ( operation == CMD_UPDATE || operation == CMD_DELETE )
+ {
+ ListCell *l;
+ foreach(l, parseTree->rtable)
+ {
+ RangeTblEntry *rte = lfirst(l);
+ rte->requiredPerms ^= ACL_SELECT;
+ }
+ }
+
/*
* Do permissions checks. It's sufficient to examine the query's top
* rangetable here --- subplan RTEs will be checked during
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] Constraint Error effect on PostgreSQL
Hi Fellow PostgreSQL users,
Just a question here.
Is there any bad effect on the PostgreSQL performance If I encounter many
fails on inserting records to database with primary key column.
For example I have this table
CREATE TABLE unique_items
(
item_id text NOT NULL,
CONSTRAINT unique_items_pkey PRIMARY KEY (item_id),
CONSTRAINT unique_item_id_fk FOREIGN KEY (item_id)
REFERENCES items (item_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH OIDS;
With these valu:
Unique items
Item001
Then I have a program that insert 1(one) million times like this:
Insert into unique_items(item_id) values('Item001)
Ofcourse we all know that it will fail because there is already a record in
the database.
Would there be any bad effect on the database or none?
Regards,
Ian
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Question re: relational technique
On Monday 13 March 2006 03:03, Richard Huxton wrote: > Robert Paulsen wrote: > > This still requires me to modify the overall database structure but not > > the original item table. As my reward :) I get to use any type I choose > > for each new attribute. > > The whole point of the database structure is to accurately reflect the > requirements of your data. If you don't want your change your structure > to keep track of the real world, why bother to structure it in the first > place? Just stick it all in text documents and let htdig free-text > search against it. Requirements change and differ from one application of the datbase to another. The database structure is maintained by others and is used by several diverse locations. It is an effort to incorporate and coordinate changes. The database already uses the name-value technique in one place, probably for this very reason. I was suspicious of the technique so posted my original question. The answer given is a resonable compromise. I can have my own table whose structure I control, even though the fields in the table "really" belong in another table. Bob ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Set generating functions and subqueries
Hi, Tom, Tom Lane wrote: > The behavior of the subquery expression is dictated by the SQL spec: > > 1) If the cardinality of a or a is > greater than 1, then an exception condition is raised: cardinal- > ity violation. That's interesting to know, and it seems to be a clean design. > The fact that the other form is even allowed is more of a holdover from > PostQUEL than something we have consciously decided is a good idea. > (IMHO it's actually a fairly *bad* idea, because it does not work nicely > when there's more than one SRF in the same targetlist.) It'll probably > get phased out someday, if we can find a way to replace the > functionality. I seem to recall speculating that SQL2003's LATERAL > tables might do the job. AFAICS, it can be replaced with JOINs: select * FROM (SELECT 'othercol' AS other) as foo CROSS JOIN (SELECT generate_series(1,2) as a) as fop CROSS JOIN (SELECT generate_series(3,4) as b) AS foq; other | a | b --+---+--- othercol | 1 | 3 othercol | 2 | 3 othercol | 1 | 4 othercol | 2 | 4 (4 rows) > No kidding. I wasn't kidding, I just wanted to point out the different behaviour between equal-length and inequal-length sequences. Thanks, markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] removing "not null" modifier
Hello,
let's say I have created a postgresql-7.2.2 db using the following cmds:
CREATE TABLE status
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(32)
);
CREATE TABLE event
(
id SERIAL NOT NULL PRIMARY KEY,
description VARCHAR(32),
status_id INTEGER NOT NULL REFERENCES status(id)
);
in psql I then see:
mydb=# \d event
Table "event"
Column| Type |Modifiers
-+---+--
id | integer | not null default
nextval('"event_id_seq"'::text)
description | character varying(32) |
status_id | integer | not null
Primary key: event_pkey
Triggers: RI_ConstraintTrigger_43210
The question:
how can I get rid of the "not null" modifier on status_id?
thanks,
Robert Urban
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] removing "not null" modifier
O Robert Urban έγραψε στις Mar 13, 2006 :
> Hello,
>
> let's say I have created a postgresql-7.2.2 db using the following cmds:
>
> CREATE TABLE status
> (
> id SERIAL NOT NULL PRIMARY KEY,
> name VARCHAR(32)
> );
>
> CREATE TABLE event
> (
> id SERIAL NOT NULL PRIMARY KEY,
> description VARCHAR(32),
> status_id INTEGER NOT NULL REFERENCES status(id)
> );
>
> in psql I then see:
>
> mydb=# \d event
> Table "event"
>Column| Type |Modifiers
>
> -+---+--
> id | integer | not null default
> nextval('"event_id_seq"'::text)
> description | character varying(32) |
> status_id | integer | not null
> Primary key: event_pkey
> Triggers: RI_ConstraintTrigger_43210
>
>
> The question:
>
> how can I get rid of the "not null" modifier on status_id?
ALTER TABLE event ALTER status_id DROP NOT NULL;
>
> thanks,
>
> Robert Urban
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>
--
-Achilleus
---(end of broadcast)---
TIP 1: 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] Ask a PostgreSql question (about select )
> Objet : Re: [SQL] Ask a PostgreSql question (about select ) > > Please post questions to the list. I'm forwarding this to the SQL > list, as I think it's probably most applicable. I don't know much > about the Oracle syntax you've used. Hopefully someone will be able > to help you. > > On Mar 13, 2006, at 12:30 , min wrote: > > > Please help me one PostgreSQL Statement, Thanks > > > > in Oracle > > select rownum,groupid,qty from abc > > --- --- > > 1 a5 3 > > 2 a2 4 > > 3 a3 5 > > 4 > > 5 > > . > > . > > . > > > > > > in PostgreSql > > > > How to wirte Statement ( Rownum -> change ??) > > PostgreSQL doesn't have the Oracle ROWNUM feature. But if you were using ROWNUM to limit a result set, you are more likely to use the LIMIT feature in PostgreSQL: http://archives.postgresql.org/pgsql-sql/2005-05/msg00127.php -- Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Permission to Select
On Mon, 2006-03-13 at 12:51 +0300, Eugene E. wrote: > Hi all > the serious problem with permissions is encountered > > NOTE: the following example is really useful but there is no room to > describe it's use. > > > db=# CREATE USER u; > db=# CREATE TABLE t (i int, a text); > db=# REVOKE all ON t FROM u; > db=# GRANT update,insert,delete ON t TO u; > db=# \c - u > > db=> INSERT INTO t VALUES (1,'x'); > INSERT > db=> UPDATE t SET a='y' WHERE i=1; > ERROR: Permission denied for relation t; > db=> UPDATE t SET a='y'; > UPDATE > > 1) The user "u" is permitted but unable to perfom the operation ! > 2) A user is able to update WHOLE table but unable to update ANY part of > it ! > Good chance this was on purpose. BEGIN; UPDATE compensation SET salary = salary WHERE name = 'Tom' and salary BETWEEN 5 and 6; -- No rows updated -- that's not Toms salary rollback; BEGIN; UPDATE compensation SET salary = salary WHERE name = 'Tom' and salary BETWEEN 6 and 7; -- One row updated so I found the range, I need a raise! rollback; By allowing the user a where clause you grant them select privileges. You will find that delete works the same way. This is one of those times when per column permissions are useful. You could grant them select access on the "name" column but not the "salary" column. -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Permission to Select
Rod Taylor wrote: > By allowing the user a where clause you grant them select privileges. > You will find that delete works the same way. > > This is one of those times when per column permissions are useful. You > could grant them select access on the "name" column but not the "salary" > column. If I understand clearly, the patch he posted modified things so that if the user issued an UPDATE command, the SELECT permission was required as well. Thus a user with UPDATE privileges but no SELECT was not allowed to execute the UPDATE command. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Set generating functions and subqueries
Markus Schaber <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> The fact that the other form is even allowed is more of a holdover from >> PostQUEL than something we have consciously decided is a good idea. >> (IMHO it's actually a fairly *bad* idea, because it does not work nicely >> when there's more than one SRF in the same targetlist.) It'll probably >> get phased out someday, if we can find a way to replace the >> functionality. I seem to recall speculating that SQL2003's LATERAL >> tables might do the job. > AFAICS, it can be replaced with JOINs: Not really; the case where joins don't help is where you want to generate the SRF's output for each of the values appearing in a table. There's an example in "SQL Functions Returning Sets" in the manual: http://www.postgresql.org/docs/8.1/static/xfunc-sql.html#AEN31646 regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Permission to Select
On Mon, 2006-03-13 at 10:46 -0400, Alvaro Herrera wrote: > Rod Taylor wrote: > > > By allowing the user a where clause you grant them select privileges. > > You will find that delete works the same way. > > > > This is one of those times when per column permissions are useful. You > > could grant them select access on the "name" column but not the "salary" > > column. > > If I understand clearly, the patch he posted modified things so that if > the user issued an UPDATE command, the SELECT permission was required as > well. Thus a user with UPDATE privileges but no SELECT was not allowed > to execute the UPDATE command. Okay, I got it backward. The exclamation mark behind the first point made me think it was an issue. What information can be retrieved from a structure by being able to update all rows? You possibly find the range of values (max/min via integer overflow) but still would not know which tuple is associated with which value. I don't have a use-case for update but the delete / insert combination is quite handy for doing data resets from a host which should not be able to retrieve the interim states. ---(end of broadcast)--- TIP 1: 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] Permission to Select
"Eugene E." <[EMAIL PROTECTED]> writes: > db=# REVOKE all ON t FROM u; > db=# GRANT update,insert,delete ON t TO u; > db=# \c - u > db=> INSERT INTO t VALUES (1,'x'); > INSERT > db=> UPDATE t SET a='y' WHERE i=1; > ERROR: Permission denied for relation t; > db=> UPDATE t SET a='y'; > UPDATE This behavior is correct and as documented in the UPDATE reference page: You must have the UPDATE privilege on the table to update it, as well as the SELECT privilege to any table whose values are read in the expressions or condition. The use of "i" in the WHERE clause is what causes SELECT privilege to be needed. If we had per-column privileges then we could be finer-grained about it, but we don't (yet). > Please examine the following patch and make your judgment: This patch is so wrong it's not even worth discussing :-( regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] connectby documentation
Hi, I'm searching for an Oracle START WITH ... CONNECT BY PRIOR ... equivalence. It seems that PostgreSQL (version >= 7.4) supports a function connectby that provides similar feature. Unfortunately I don't find any documentation on that function. Could you please give me a link on such documentation? Note: Just an example of the Oracle START WITH ... CONNECT BY PRIOR ... behaviour. 4| || 2| / \ | 1 3 | Hierarchy dependency order / \ | | 5 | 6 | |/ | 7V SELECT JobId, JobParentId FROM JobDependency START WITH JobParentId IN ( _Root_Datamarts_ ) CONNECT BY PRIOR JobId = JobParentId JOBID JOBPARENTID - --- 2 4 1 2 5 1 7 1 3 2 6 3 7 6 Regards, -- Daniel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] removing "not null" modifier
Achilleus Mantzios <[EMAIL PROTECTED]> writes: > O Robert Urban Ýãñáøå óôéò Mar 13, 2006 : >> how can I get rid of the "not null" modifier on status_id? > ALTER TABLE event ALTER status_id DROP NOT NULL; I don't think 7.2 has that. Of course, there are many excellent reasons why Robert needs to get himself off 7.2.2 ASAP. Some of them can be found here: http://developer.postgresql.org/docs/postgres/release.html regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] connectby documentation
On Mon, Mar 13, 2006 at 10:37:37AM -0500, Daniel Caune wrote: > I'm searching for an Oracle START WITH ... CONNECT BY PRIOR ... > equivalence. It seems that PostgreSQL (version >= 7.4) supports a > function connectby that provides similar feature. Unfortunately I don't > find any documentation on that function. Could you please give me a > link on such documentation? connectby() is part of the contrib/tablefunc module. You'll need to install that module and load it into your database. Somebody has made a CONNECT BY patch but the developers have objected to it for various reasons. Search the list archives for discussion. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Constraint Error effect on PostgreSQL
Christian Paul B. Cosinas wrote:
Then I have a program that insert 1(one) million times like this:
Insert into unique_items(item_id) values('Item001)
Ofcourse we all know that it will fail because there is already a record in
the database.
Would there be any bad effect on the database or none?
No long-term effects. Obviously it takes time to run one million queries
even if they are all inserts that fail.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Permission to Select
What information can be retrieved from a structure by being able to update all rows? Write a plpgsql function snoop(x) which inserts x into a table 'log' created by you, and also returns x. UPDATE users SET password=snoop(password). Read log table. Done. If you have WHERE rights on a table, you can guess any column content pretty quick. Just do a binary search. Some time ago I had a friend whose website had some SQL injection holes, and he said "so what ? who cares ? I have magicquotes" (lol), so I coded a python script which injected a "password BETWEEN x AND y" (using CHR() to avoid quotes) and narrowed the search. It took about 1 minute to get the password (which turned out to be md5 that resisted a few seconds to dictionary attack using whatever evil hax0rz tool). ---(end of broadcast)--- TIP 1: 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] connectby documentation
> -Message d'origine- > De : Michael Fuhr [mailto:[EMAIL PROTECTED] > Envoyé : lundi, mars 13, 2006 11:12 > À : Daniel Caune > Cc : postgresql sql list > Objet : Re: [SQL] connectby documentation > > On Mon, Mar 13, 2006 at 10:37:37AM -0500, Daniel Caune wrote: > > I'm searching for an Oracle START WITH ... CONNECT BY PRIOR ... > > equivalence. It seems that PostgreSQL (version >= 7.4) supports a > > function connectby that provides similar feature. Unfortunately I don't > > find any documentation on that function. Could you please give me a > > link on such documentation? > > connectby() is part of the contrib/tablefunc module. You'll need > to install that module and load it into your database. > > Somebody has made a CONNECT BY patch but the developers have objected > to it for various reasons. Search the list archives for discussion. > That sounds good. I tried to install PostgreSQL contrib modules on my Linux/Debian distribution: > apt-get install postgresql-contrib (...) The following extra packages will be installed: libpq3 libxml2 postgresql postgresql-7.4 postgresql-client postgresql-client-7.4 postgresql-contrib-7.4 (...) 7.4?! Huh... Is there any sources.list a bit more updated? Where can I download PostgreSQL contrib modules. The documentation 8.1 doesn't help so much. Where can I find more documentation on available contrib. modules? Thanks, -- Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] connectby documentation
7.4?! Huh... Is there any sources.list a bit more updated? Where can I download PostgreSQL contrib modules. The documentation 8.1 doesn't help so much. Where can I find more documentation on available contrib. modules? gentoo automatically compiles and installs the contribs (you just have to execute the SQL scripts for the pnes you want to put in your database). Maybe you already have them ? trl "locate contrib", who knows... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] connectby documentation
> "outdated" packets is unfortunately a big issue on Debian. If you want > to have up-to-date apt-packages try > > www.backports.org > > Add one of the mirrors from the list to your sources.list, then run > apt-get update and then try to install again ... > :-) And you'll see, that you can install newer versions than 7.4 :-) > Sorry, this is not my day: "apt-get install postgresql-contrib-8.1" works fine... Just a link on the documentation that fully explains how connectby() works would be great! :-) Thanks, -- Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Update value to "the first character is capital and the rest is small"
Hello all, Does anyone have available plpgsql codes to update all capital letters in a column to "the first character is capital and the rest is small" ? For example, in tableA(id, description) 001, 'ZHANG ZHE XIN' => 'Zhang Zhe Xin' 002, 'LIU, WEI-HUAI' =>'Liu, Wei-Huai' 003, 'GU & WEI. NAN (CE SHI) & TOMMORROW' => 'Gu & Wei. Nan (Ce Shi) & Tommorrow' Thanks a lot! Ying ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Update value to "the first character is capital and the rest is small"
Emi Lu wrote:
> Hello all,
>
> Does anyone have available plpgsql codes to update all capital letters
> in a column to "the first character is capital and the rest is small" ?
I don't know about plpgsql codes, but there is a function initcap() that
you can use for that.
alvherre=# select initcap('GU & WEI. NAN (CE SHI) & TOMMORROW');
initcap
Gu & Wei. Nan (Ce Shi) & Tommorrow
(1 row)
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] [GENERAL] Update value to "the first character is capital and
I got the answer from the docs. |initcap|(text) thanks anyway, Ying Hello all, Does anyone have available plpgsql codes to update all capital letters in a column to "the first character is capital and the rest is small" ? For example, in tableA(id, description) 001, 'ZHANG ZHE XIN' =>'Zhang Zhe Xin' 002, 'LIU, WEI-HUAI' =>'Liu, Wei-Huai' 003, 'GU & WEI. NAN (CE SHI) & TOMMORROW' => 'Gu & Wei. Nan (Ce Shi) & Tommorrow' Thanks a lot! Ying ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] connectby documentation
On Mon, Mar 13, 2006 at 04:56:39PM -0500, Daniel Caune wrote: > Sorry, this is not my day: "apt-get install postgresql-contrib-8.1" > works fine... Just a link on the documentation that fully explains how > connectby() works would be great! :-) The contrib package should have installed a file named README.tablefunc. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [SQL] Update value to "the first character is capital
I saw it from the docs as well. Thank you Alvaro :)
Alvaro Herrera wrote:
Emi Lu wrote:
Hello all,
Does anyone have available plpgsql codes to update all capital letters
in a column to "the first character is capital and the rest is small" ?
I don't know about plpgsql codes, but there is a function initcap() that
you can use for that.
alvherre=# select initcap('GU & WEI. NAN (CE SHI) & TOMMORROW');
initcap
Gu & Wei. Nan (Ce Shi) & Tommorrow
(1 row)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] connectby documentation
> > Sorry, this is not my day: "apt-get install postgresql-contrib-8.1" > > works fine... Just a link on the documentation that fully explains how > > connectby() works would be great! :-) > > The contrib package should have installed a file named README.tablefunc. > You are right. The documentation is located in /usr/share/doc/postgresql-contrib-8.1/ . Wow, that was the quest for the Holy Grail! :-) -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] connectby documentation
> > > Sorry, this is not my day: "apt-get install postgresql-contrib-8.1" > > > works fine... Just a link on the documentation that fully explains > how > > > connectby() works would be great! :-) > > > > The contrib package should have installed a file named > README.tablefunc. > > > > You are right. The documentation is located in > /usr/share/doc/postgresql-contrib-8.1/ . > > Wow, that was the quest for the Holy Grail! :-) > Huh... It seems that installing the package postgresql-contrib does not make the work itself. I provide hereafter a description about how to install the function connectby (I didn't find such documentation and I don't know where to write this documentation): > apt-get install postgresql-contrib-8.1 > emacs /usr/share/postgresql/8.1/contrib/tablefunc.sql Replace "$libdir" by "/usr/lib/postgresql/8.1/lib/tablefunc.so" (I suggest to modifying only a copy of this file). > su postgres > psql -f /usr/share/postgresql/8.1/contrib/tablefunc.sql You can check that the work is done as follows: > psql => \df connectby List of functions Schema | Name| Result data type | Argument data types +---+--+ - public | connectby | setof record | text, text, text, text, integer public | connectby | setof record | text, text, text, text, integer, text public | connectby | setof record | text, text, text, text, text, integer, text I hope that will help another PostgreSQL newbie. -- Daniel ---(end of broadcast)--- TIP 1: 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] connectby documentation
On Mon, Mar 13, 2006 at 06:38:14PM -0500, Daniel Caune wrote: > I provide hereafter a description about how to install the function > connectby (I didn't find such documentation and I don't know where to > write this documentation): README.tablefunc contains instructions on how to load the module into a database. > > apt-get install postgresql-contrib-8.1 > > emacs /usr/share/postgresql/8.1/contrib/tablefunc.sql > > Replace "$libdir" by "/usr/lib/postgresql/8.1/lib/tablefunc.so" (I > suggest to modifying only a copy of this file). That shouldn't be necessary unless the package installed the shared objects somewhere other than where the database was expecting. What's the output of "pg_config --libdir --version"? > > su postgres > > psql -f /usr/share/postgresql/8.1/contrib/tablefunc.sql Or, omitting the su, psql -U postgres -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] connectby documentation
> -Message d'origine- > De : Michael Fuhr [mailto:[EMAIL PROTECTED] > Envoyé : lundi, mars 13, 2006 19:26 > À : Daniel Caune > Cc : [EMAIL PROTECTED]; postgresql sql list > Objet : Re: [SQL] connectby documentation > > On Mon, Mar 13, 2006 at 06:38:14PM -0500, Daniel Caune wrote: > > I provide hereafter a description about how to install the function > > connectby (I didn't find such documentation and I don't know where to > > write this documentation): > > README.tablefunc contains instructions on how to load the module > into a database. > > > > apt-get install postgresql-contrib-8.1 > > > emacs /usr/share/postgresql/8.1/contrib/tablefunc.sql > > > > Replace "$libdir" by "/usr/lib/postgresql/8.1/lib/tablefunc.so" (I > > suggest to modifying only a copy of this file). > > That shouldn't be necessary unless the package installed the shared > objects somewhere other than where the database was expecting. > What's the output of "pg_config --libdir --version"? > /usr/lib PostgreSQL 8.1.2 > > > su postgres > > > psql -f /usr/share/postgresql/8.1/contrib/tablefunc.sql > > Or, omitting the su, psql -U postgres > > -- > Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] connectby documentation
On Mon, Mar 13, 2006 at 07:29:44PM -0500, Daniel Caune wrote: > > > Replace "$libdir" by "/usr/lib/postgresql/8.1/lib/tablefunc.so" (I > > > suggest to modifying only a copy of this file). > > > > That shouldn't be necessary unless the package installed the shared > > objects somewhere other than where the database was expecting. > > What's the output of "pg_config --libdir --version"? > > /usr/lib > PostgreSQL 8.1.2 Sorry, that should have been --pkglibdir. If it shows the same thing then apparently the contrib module and the database have different notions of where the database's library directory is (assuming that pg_config and the postmaster agree). Did the module and the database come from the same source? If so then the originator's packaging could use improving. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] unsubscribe
unsubscribe __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] unsubscribe
unsubscribe
[SQL] unsubscribe
unsubscribe This message is intended only for the use of the individual(s) or entity to which it is addressed and may contain information that is privileged, confidential, and/or proprietary to RealPage and its affiliated companies. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, forwarding or copying of this communication is prohibited without the express permission of the sender. If you have received this communication in error, please notify the sender immediately and delete the original message.
Re: [SQL] Permission to Select
Tom Lane wrote: "Eugene E." <[EMAIL PROTECTED]> writes: db=# REVOKE all ON t FROM u; db=# GRANT update,insert,delete ON t TO u; db=# \c - u db=> INSERT INTO t VALUES (1,'x'); INSERT db=> UPDATE t SET a='y' WHERE i=1; ERROR: Permission denied for relation t; db=> UPDATE t SET a='y'; UPDATE This behavior is correct and as documented in the UPDATE reference page: Good if you have a strange behavior - just document it. quite good. You must have the UPDATE privilege on the table to update it, as well as the SELECT privilege to any table whose values are read in the expressions or condition. This means that some privileges are NOT INDEPENDENT. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Permission to Select
PFC wrote: What information can be retrieved from a structure by being able to update all rows? Write a plpgsql function snoop(x) which inserts x into a table 'log' created by you, and also returns x. UPDATE users SET password=snoop(password). Read log table. Done. This trick is available _NOW_. (in the current state of permission system) ---(end of broadcast)--- TIP 1: 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] Permission to Select -- I am wrong
Eugene E. wrote: PFC wrote: What information can be retrieved from a structure by being able to update all rows? Write a plpgsql function snoop(x) which inserts x into a table 'log' created by you, and also returns x. UPDATE users SET password=snoop(password). Read log table. Done. This trick is available _NOW_. (in the current state of permission system) I AM WRONG ! sorry. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Copying a row within table
Hi people, testing=# INSERT INTO foo (SELECT * FROM foo WHERE foo_id = 2); ERROR: duplicate key violates unique constraint "foo_pkey" testing=# testing=# INSERT INTO foo (foo_1, foo_2, foo_3 ...) (SELECT foo_1, foo_2, foo_3 ... FROM message_table WHERE foo_id = 10); INSERT 717286 1 testing=# Is there a fast way to copy all but not the PK column to a new row within the same table so that the new foo_id gets its value from the sequence ? TIA and BR, Aarni -- Aarni Ruuhimäki -- This is a bugfree broadcast to you from **Kmail** on **Fedora Core** linux system -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
