[SQL] count array in postgresql
hello guys, I need some help, I have created function with argument in array. I already try to find array function in manual but failed. so is there any solution to counting array value and looping array value? some language usually use count($array) Thank in a bunch Ricky ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] count array in plpgsql
hello guys, I need some help, I have created function with argument in array. I already try to find array function in manual but I cant. so is there any solution to counting array value and looping array value? any some language usually use count($array) Thank in a bunch Ricky Wibowo -- Gutten Aben Sugeng Sonten, Jangane Kurang Santen bandeng ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Links between rows in a table
On Sun, Mar 06, 2005 at 05:42:14 +0100, Stefan Weiss <[EMAIL PROTECTED]> wrote: > > We are currently designing a web-based application in which users can > add other users as "friends". These links are bi-directional, meaning > that when A adds B to his friends, he is automatically one of B's > friends. Eventually we will have to add a feature that shows how A is This doesn't seem like a good idea unless the person getting linked to gets to confirm he wants the link creator as a friend. > I can see several ways how such links could be modeled in a relational > database, but I was wondering if there was some tried-and-true recipe > that would spare me from reinventing the wheel. Putting aside for the > moment everything but the links, the simplest way of connecting users > would be a "friends" table (user_id int, friend_id int). We could get a > user's friends with a simple query like this: > > SELECT friend_id FROM friends WHERE user_id = X > UNION SELECT user_id FROM friends WHERE friend_id = X; It would probably be better to always have either both or neither of the symmetric relationships in the table. You could make a set of triggers to enforce this. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Links between rows in a table
On 2005-03-06 18:42, Bruno Wolff III wrote:
>> We are currently designing a web-based application in which users can
>> add other users as "friends". These links are bi-directional, meaning
>> that when A adds B to his friends, he is automatically one of B's
>> friends. Eventually we will have to add a feature that shows how A is
>
> This doesn't seem like a good idea unless the person getting linked to
> gets to confirm he wants the link creator as a friend.
Yes, we have an invitation/pending/confirm process, and users are also
able to block other users. I haven't mentioned this because I did not
think it relevant to the storage question. There is a different system
for unilateral friendships ("favorites/fans").
>> SELECT friend_id FROM friends WHERE user_id = X
>> UNION SELECT user_id FROM friends WHERE friend_id = X;
>
> It would probably be better to always have either both or neither of
> the symmetric relationships in the table. You could make a set of triggers
> to enforce this.
We have also considered this, but since "friendship" in this application
is mutual by definition, wouldn't that just lead to data duplication? We
might still insert two rows instead of one, if we find that the union
slows things down more than the larger table, or if the "connection
finder" feature will be easier to implement that way.
By the way, according to the MySQL documentation, "Rudimentary support
for triggers is included beginning with MySQL 5.0.2". The MySQL
compatibility requirement is none of my doing, I have given up trying to
educate my customers about the benefits of a real database...
regards,
stefan weiss
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] count array in postgresql
On Sun, Mar 06, 2005 at 11:54:15PM +0700, bandeng wrote: > I need some help, I have created function with argument in array. > I already try to find array function in manual but failed. Maybe you're looking for "Array Functions and Operators" in the "Functions and Operators" chapter. You don't say what version of PostgreSQL you're using; here's a link to the 8.0 documentation: http://www.postgresql.org/docs/8.0/interactive/functions-array.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Links between rows in a table
It would probably be better to always have either both or neither of the symmetric relationships in the table. You could make a set of triggers to enforce this. Because your relation is symmetric, you should not name them "user" and "friend". The duplication is useless if you add a constraint : see this create table friendship ( user_id_1 integer references ... on delete cascade, user_id_2 integer references ... on delete cascade, CHECK( user_id_1 < user_id_2 ) ); user_id_1 < user_id_2 means : - a user can't be his own friend - only one row per friend - when you want to know if A is friend of B, no need to make two selects, just select where user_id_1 = min(user_id_A, user_id_B) AND user_id_2 = max(user_id_A, user_id_B) To get the list of friends for a user, you still need the union, but that is no real problem. Making two queries will be marginally slower than one query on a bigger table, but youu save precious cache space, so in the end it could be faster. ---(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] Postgres performance
Really ? In my experience this is a sure way to get inconsistencies slowly creeping into your database, and you also get a load of funky concurrency issues. Yes, you are rigth... my insert/update are very simple and without problems and so I think to use 'foreign key' coded to make faster/simpler the management and don't overloading the db (and use exception code management )... but I had a problem with pgSQL because server was very busy and the same query was replicate (because users refresh the page... :( ) so now I've foreign keys... It's a typical case, program being stopped between insertion of parent and child row. Although in this case FK's is not the solution, transactions are. What about it? (i think it's the same with mySQL...) I don't know how users can connect to... 1, 10, 1000... I must create a pool with 1000 connections? is this fine? if connections aren't released I must reset manually, it is dangerous... do you think? Just set the max number of connections for postgres a bit higher than the max number of apache processes in apache.conf. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Building a database from a flat file
On 2005-03-03, Markus Schaber <[EMAIL PROTECTED]> wrote: > - Create the new date in another schema, and then simply rename those > two schemas for "switch over" This kind of thing looks superficially attractive but it has some serious problems if you try and do it while there is other database activity. Specifically, cached plans for functions and prepared statements will not be invalidated by the rename, and will still refer to the old tables, so you may have inconsistent results. > - Have two different set of tables (maybe two identical schemas), and > let your application work on a set of views. Then you can change the > views via "create or replace view" for switch over. Same problem applies here. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(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] Links between rows in a table
On 2005-03-06 20:26, PFC wrote: > Because your relation is symmetric, you should not name them "user" and > > "friend". A good point, thank you. > user_id_1 < user_id_2 means : > - a user can't be his own friend > - only one row per friend > - when you want to know if A is friend of B, no need to make two > selects, > just select where user_id_1 = min(user_id_A, user_id_B) AND user_id_2 = > max(user_id_A, user_id_B) This is what we were planning to do on the application side, but a CHECK constraint is even better. It will be used and enforced by those DB engines that understand it, and ignored by the one engine that doesn't. > To get the list of friends for a user, you still need the union, but > that > is no real problem. Making two queries will be marginally slower than one > query on a bigger table, but youu save precious cache space, so in the end > it could be faster. Thank you for your insight. We will rename the columns, add the CHECK and go ahead with this setup. regards, stefan weiss ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] count array in postgresql
my Postgresql version is 7.4
I still cannot find count function in that reference. but I try to use
like this just checking the array value is null or not, it's work but
dunno it is the good way or not.
for example,
vararray := {ab,cd,ef,gh}
i := 1;
while vararray[i] is not null loop
i := i + 1;
end loop;
raise info i;
On Sun, 6 Mar 2005 12:10:55 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Sun, Mar 06, 2005 at 11:54:15PM +0700, bandeng wrote:
>
> > I need some help, I have created function with argument in array.
> > I already try to find array function in manual but failed.
>
> Maybe you're looking for "Array Functions and Operators" in the
> "Functions and Operators" chapter. You don't say what version of
> PostgreSQL you're using; here's a link to the 8.0 documentation:
>
> http://www.postgresql.org/docs/8.0/interactive/functions-array.html
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
--
Gutten Aben Sugeng Sonten, Jangane Kurang Santen
bandeng
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] count array in postgresql
How about array_dims? It looks like you will still have to do a string
split, but
Sean
- Original Message -
From: "bandeng" <[EMAIL PROTECTED]>
To:
Sent: Sunday, March 06, 2005 9:09 PM
Subject: Re: [SQL] count array in postgresql
my Postgresql version is 7.4
I still cannot find count function in that reference. but I try to use
like this just checking the array value is null or not, it's work but
dunno it is the good way or not.
for example,
vararray := {ab,cd,ef,gh}
i := 1;
while vararray[i] is not null loop
i := i + 1;
end loop;
raise info i;
On Sun, 6 Mar 2005 12:10:55 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Sun, Mar 06, 2005 at 11:54:15PM +0700, bandeng wrote:
> I need some help, I have created function with argument in array.
> I already try to find array function in manual but failed.
Maybe you're looking for "Array Functions and Operators" in the
"Functions and Operators" chapter. You don't say what version of
PostgreSQL you're using; here's a link to the 8.0 documentation:
http://www.postgresql.org/docs/8.0/interactive/functions-array.html
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
--
Gutten Aben Sugeng Sonten, Jangane Kurang Santen
bandeng
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] count array in postgresql
On Mon, Mar 07, 2005 at 09:09:57AM +0700, bandeng wrote: > I still cannot find count function in that reference. See array_upper() and array_lower(). -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
