[SQL] schema proxying virtual database
[ sorry if it is a repost, i am not sure if i am subscribed in -general ] Dear List, There are many opensource applications that support postgresql (eg , gforge , bricolage , dspam ..) but does not use schemas(namespaces) as a result of which you are forced to use/create a new database and loose the advantage of linking the application data with your existing database. as no-cross database queries can be done in PG. my question is , is it a feasible idea to have some special kind of database in the postgresql cluster that mimics a schema of an existsing database. say rt3 is a special database that links to existing maindb databases' rt3 namespace then any DDL / DML done to public schema of rt3 database is redirected to rt3 namespace of the existing database maindb's rt3 schema. regds mallah.
Re: [SQL] Must I use DISTINCT?
have you tried Join using , eg SELECT e.eid, e.name FROM entry e join access a ON( e.eid = 120 AND (e.ownid = 66 OR e.aid = a.aid) ) ; some sample data might also help in understanding the prob more clrearly. regds rajesh kumar mallah. On Fri, Feb 6, 2009 at 3:27 AM, Michael B Allen iop...@gmail.com wrote: Please consider the following SQL SELECT e.eid, e.name FROM entry e, access a WHERE e.eid = 120 AND (e.ownid = 66 OR e.aid = a.aid) The intent is to match one entry with the eid of 120. However I would like to impose an additional constraint that either e.ownid must be 66 or e.aid must match the aid of an entry in the access table (there's actually a lot more to the query but I think this should be sufficient to illustrate my problem). The problem is that the e.ownid is 66 and therefore the same entry is returned for each access entry. Of course I can simply SELECT DISTINCT but that seems like an improper usage of DISTINCT here. Is there an alternative way to write this query? I only want to select from the access table for the purpose of constraining by aid. Mike -- Michael B Allen Java Active Directory Integration http://www.ioplex.com/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] (possible) bug with constraint exclusion
On Jan 12, 2008 10:54 AM, Tom Lane [EMAIL PROTECTED] wrote: Rajesh Kumar Mallah [EMAIL PROTECTED] writes: Am I correct in understanding that the current behavior is inappropriate and shall be corrected at some point of time in future versions ? It's a bug, it's patched: http://archives.postgresql.org/pgsql-committers/2008-01/msg00184.php Thanks for the (unbelievable) quick response, I applied the patch on my production server and the problem is gone. tradein_clients= \pset null NULL Null display is NULL. tradein_clients= SELECT * from temp.x where id is NULL; id -- NULL (1 row) tradein_clients= SELECT * from temp.x ; id -- 1 2 NULL (3 rows) tradein_clients= Regds mallah. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] (possible) bug with constraint exclusion
On Jan 12, 2008 1:26 AM, Tom Lane [EMAIL PROTECTED] wrote: Rajesh Kumar Mallah [EMAIL PROTECTED] writes: looks like constraint exclusion is being too aggressive in excluding null values Hmm, you're right. Looks like I broke it here: http://archives.postgresql.org/pgsql-committers/2007-05/msg00187.php although its well known that check constraints apply on not null values only. No, that is not a correct statement either --- it's exactly that type of sloppy thinking that got me into trouble with this patch :-( The problem is that predicate_refuted_by_simple_clause() is failing to distinguish whether refutes means proves false or proves not true. For constraint exclusion we have to use the stricter proves false interpretation, and in that scenario a clause foo IS NULL fails to refute a check constraint foo 0, because the latter will produce NULL which isn't false and therefore doesn't cause the check constraint to fail. The motivation for that patch was to support IS NULL as one partition of a partitioned table. Thinking about it I see that if the other partitions have check constraints like foo 0 then the partitioning is actually incorrect, because the other check constraints are failing to exclude NULLs. The right way to set up such a partitioned table is to include foo IS NOT NULL as part of the check constraint, or as a special-purpose NOT NULL flag, except in the IS NULL partition. The current constraint exclusion logic fails to notice attnotnull, though. So the correct fix seems to be: Dear Tom, Thanks for the elaborate explanation on your part, owing to my limitations I could not understand all the parts of it. Am I correct in understanding that the current behavior is inappropriate and shall be corrected at some point of time in future versions ? thanks once again to all the developers for making PostgreSQL. regds mallah. * Fix predicate_refuted_by_simple_clause to not suppose that a strict operator is proved FALSE by an IS NULL clause. * Fix relation_excluded_by_constraints to add foo IS NOT NULL clauses to the constraint list for attnotnull columns (perhaps this should be pushed into get_relation_constraints?). This buys back the loss of exclusion from the other change, so long as the partitioning is done correctly. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] (possible) bug with constraint exclusion
Hi , looks like constraint exclusion is being too aggressive in excluding null values although its well known that check constraints apply on not null values only. Hope the minimal test session below explains the problem we facing. BTW: we are very impressed with the performance gains we achieved by partitioning a table recently. tradein_clients= SELECT version(); version --- PostgreSQL 8.2.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9) (1 row) tradein_clients= \pset null NULL tradein_clients= \d x Table temp.x Column | Type | Modifiers +-+--- id | integer | Check constraints: x_id_check CHECK (id 0) tradein_clients= SELECT * from x; id -- 1 2 NULL (3 rows) tradein_clients= explain SELECT * from x where id is null; QUERY PLAN -- Result (cost=0.00..0.01 rows=1 width=0) One-Time Filter: false (2 rows) tradein_clients= SELECT * from x where id is null; id (0 rows) tradein_clients= SET constraint_exclusion to off; SET tradein_clients= SELECT * from x where id is null; id -- NULL (1 row) tradein_clients= Regds mallah. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] (possible) bug with constraint exclusion
Update the phenomenon does not exists in 8.2.0 but exists in 8.2.5. On Jan 11, 2008 12:28 PM, Rajesh Kumar Mallah [EMAIL PROTECTED] wrote: Hi , looks like constraint exclusion is being too aggressive in excluding null values although its well known that check constraints apply on not null values only. Hope the minimal test session below explains the problem we facing. BTW: we are very impressed with the performance gains we achieved by partitioning a table recently. tradein_clients= SELECT version(); version --- PostgreSQL 8.2.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9) (1 row) tradein_clients= \pset null NULL tradein_clients= \d x Table temp.x Column | Type | Modifiers +-+--- id | integer | Check constraints: x_id_check CHECK (id 0) tradein_clients= SELECT * from x; id -- 1 2 NULL (3 rows) tradein_clients= explain SELECT * from x where id is null; QUERY PLAN -- Result (cost=0.00..0.01 rows=1 width=0) One-Time Filter: false (2 rows) tradein_clients= SELECT * from x where id is null; id (0 rows) tradein_clients= SET constraint_exclusion to off; SET tradein_clients= SELECT * from x where id is null; id -- NULL (1 row) tradein_clients= Regds mallah. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Empty Table
psql TRUNCATE TABLE tablename; if you have too many tables , generate the above commands by using a query on tables information schema table. hope it helps. On 3/29/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi list, I have many tables with many constraints and I would like to empty all my tables. There is any simple way to do it ? I have pgAdmin here but not psql. Any help would be glad. Regards Ezequias -- Ezequias Rodrigues da Rocha ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(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] Empty Table
I dont think i clearly understand your requirement. Are you wanting to restore the PLAIN backup of a database with a different database name ? what do you mean deleting information of current database ... I have not used pgAdmin as i prefer cmd line mostly. eager to help, (expect my next reply aft 6 hrs , i am abt to crash now). hope someone else helps u in meantime. regds On 3/30/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Thank you all for a so fast reply. I did a backup PLAIN with pgAdmin and I had to delete the information of the current database (the database I used to make the backup). I think that's why pgAdmin does not work with plain backups on Restore. What does occurs with the information schema when I restore from one database with a name to other with another name ? Regards Ezequias Em Thu, 29 Mar 2007 23:46:31 +0530 Rajesh Kumar Mallah [EMAIL PROTECTED] escreveu: psql TRUNCATE TABLE tablename; if you have too many tables , generate the above commands by using a query on tables information schema table. hope it helps. On 3/29/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi list, I have many tables with many constraints and I would like to empty all my tables. There is any simple way to do it ? I have pgAdmin here but not psql. Any help would be glad. Regards Ezequias -- Ezequias Rodrigues da Rocha ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(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 -- Ezequias Rodrigues da Rocha ---(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] Empty Table
I dont think i clearly understand your requirement. Are you wanting to restore the PLAIN backup of a database with a different database name ? Yes Are you getting any particular error? what platform are you in ? what do you mean deleting information of current database ... Clear all lines where the name of my orign database is mentioned in the .sql file. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] selecting random row values in postgres
On 2/24/07, Sumeet [EMAIL PROTECTED] wrote: Hi all, I'm trying to write a query to select random values from a set of 'GROUP BY' see the scenario below to understand the problem here (the actual problem cannot be discussed here so i'm taking an example scenario) Assume there is a table id | name | year_of_birth query: I want to select for each year_of_birth a random name. Dear Sumeet postgresql DISTINCT ON may be of help , but its not standard sql. regds mallah. tradein_clients= SELECT * from temp.test; ++--+-+ | id | name | yob | ++--+-+ | 1 | A| 2 | | 2 | B| 2 | | 3 | C| 2 | | 4 | D| 1 | | 5 | E| 1 | | 6 | F| 1 | ++--+-+ (6 rows) tradein_clients= SELECT distinct on (yob) id,name,yob from temp.test order by yob,random(); ++--+-+ | id | name | yob | ++--+-+ | 5 | E| 1 | | 1 | A| 2 | ++--+-+ (2 rows) tradein_clients= SELECT distinct on (yob) id,name,yob from temp.test order by yob,random(); ++--+-+ | id | name | yob | ++--+-+ | 4 | D| 1 | | 1 | A| 2 | ++--+-+ (2 rows) -- so i do a group by year_of_birth, now i have a set of names, is there any function to select just one name from these set of names. The current approach i'm using to solve this problem is 1) getting these names in a single string using a custom function 'group_concat' 2) Convert the single string into an array 3) use postgresql random function to generate a random number 4) us the random number to select a element from the array previously created. The solution is there but it's kinda hack, is there any other better way of solving this problem. Thanks, Sumeet
Re: [SQL] selecting random row values in postgres
On 2/24/07, Sumeet [EMAIL PROTECTED] wrote: got itI just figured out that i dont need the ORDER BY clause even the first row selected by the 'DISTINCT ON' would solve the problem. Dear Sumeet, if order by is not done there is no certainty about which row gets selected. usually same row keeps getting selected. but if you want a really random rows to come order by is required. if you need certainty about the particular row being selected order by a non random() column is required. regds mallah. Thanks for all you help -Sumeet. On 2/23/07, Sumeet [EMAIL PROTECTED] wrote: Thanks Buddy, really appreciate ur help on this problem solved... Is there any way this query can be optimized...i'm running it on a huge table with joins - Sumeet On 2/23/07, Rajesh Kumar Mallah [EMAIL PROTECTED] wrote: On 2/24/07, Sumeet [EMAIL PROTECTED] wrote: Hi all, I'm trying to write a query to select random values from a set of 'GROUP BY' see the scenario below to understand the problem here (the actual problem cannot be discussed here so i'm taking an example scenario) Assume there is a table id | name | year_of_birth query: I want to select for each year_of_birth a random name. Dear Sumeet postgresql DISTINCT ON may be of help , but its not standard sql. regds mallah. tradein_clients= SELECT * from temp.test; ++--+-+ | id | name | yob | ++--+-+ | 1 | A| 2 | | 2 | B| 2 | | 3 | C| 2 | | 4 | D| 1 | | 5 | E| 1 | | 6 | F| 1 | ++--+-+ (6 rows) tradein_clients= SELECT distinct on (yob) id,name,yob from temp.testorder by yob,random(); ++--+-+ | id | name | yob | ++--+-+ | 5 | E| 1 | | 1 | A| 2 | ++--+-+ (2 rows) tradein_clients= SELECT distinct on (yob) id,name,yob from temp.testorder by yob,random(); ++--+-+ | id | name | yob | ++--+-+ | 4 | D| 1 | | 1 | A| 2 | ++--+-+ (2 rows) -- so i do a group by year_of_birth, now i have a set of names, is there any function to select just one name from these set of names. The current approach i'm using to solve this problem is 1) getting these names in a single string using a custom function 'group_concat' 2) Convert the single string into an array 3) use postgresql random function to generate a random number 4) us the random number to select a element from the array previously created. The solution is there but it's kinda hack, is there any other better way of solving this problem. Thanks, Sumeet -- Thanks, Sumeet Ambre Master of Information Science Candidate, Indiana University. -- Thanks, Sumeet Ambre Master of Information Science Candidate, Indiana University.
[SQL] ordering of selected rows from an ordered subselect
Hi, we know that rows in a table are not stored in any particular order and explicit order by clause is required to get data in any particular order. but does it apply to select queries from ordered subselects also ? eg select id , name , expensive_func(name) from ( select id , name from tab order by c1 desc limit 15) as foo ; is it guaranteed that the final result is order by c1 ? Regds mallah. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] subquery abnormal behavior
On 12/11/06, Shoaib Mir [EMAIL PROTECTED] wrote: I just noticed an abnormal behavior for the subquery: create table myt1 (a numeric); create table myt2 (b numeric); select a from myt1 where a in (select a from myt2); This should be giving an error that column 'a' does not exist in myt2 but it runs with any error... I had been trying it on 8.2! Even in 8.1.5 it does not complain. (its not 8.2 specific at least) Regds mallah. Can someone please comment? Thank you, - Shoaib Mir EnterpriseDB (www.enterprisedb.com) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Proper way of iterating over the column names in a trigger function.
Hi,
I am trying to interate over column names of a table on which a C trigger
function is called on UPDATE/DELETE and INSERT. SPI function
char * SPI_fname(TupleDesc rowdesc, int colnumber)
is being used. looks like the function is returning column names like
pg.dropped.2 for deleted colnumbers. My question is
what is the proper way for iterating over column names of a table using
SPI_* functions.
(sorry for bad english)
SQL transcript:
---
tradein_clients= UPDATE public.test set x=10;
NOTICE: no of column : 2
NOTICE: colname: x
NOTICE: colname: y
UPDATE 1
tradein_clients= ALTER TABLE public.test add z int;
ALTER TABLE
tradein_clients= UPDATE public.test set x=10;
NOTICE: no of column : 3
NOTICE: colname: x
NOTICE: colname: y
NOTICE: colname: z
UPDATE 1
tradein_clients= ALTER TABLE public.test DROP y;
ALTER TABLE
tradein_clients= UPDATE public.test set x=10;
NOTICE: no of column : 2
NOTICE: colname: x
NOTICE: colname: pg.dropped.2
UPDATE 1
tradein_clients=
i am pasting the minial code based on
http://www.postgresql.org/docs/8.1/static/trigger-example.html
--
#include postgres.h
#include executor/spi.h
#include commands/trigger.h
extern Datum trigf(PG_FUNCTION_ARGS);
static int get_no_columns(char *table_name, char *table_nspname);
PG_FUNCTION_INFO_V1(trigf);
Datum
trigf(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo-context;
TupleDesc tupdesc;
HeapTuple rettuple;
char
*source_table,*source_table_nspname , *col_name ;
int ret,ncolumns, i ;
/* make sure it's called as a trigger at all */
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, trigf: not called by trigger manager);
/* tuple to return to executor */
if (TRIGGER_FIRED_BY_UPDATE(trigdata-tg_event))
rettuple = trigdata-tg_newtuple;
else
rettuple = trigdata-tg_trigtuple;
tupdesc = trigdata-tg_relation-rd_att;
/* connect to SPI manager */
if ((ret = SPI_connect()) 0)
elog(INFO, trigf : SPI_connect returned %d, ret);
source_table = SPI_getrelname(trigdata-tg_relation);
source_table_nspname = SPI_getnspname(trigdata-tg_relation);
ncolumns = get_no_columns( source_table,source_table_nspname );
elog(NOTICE, no of column : %d , ncolumns);
for (i = 1; i = ncolumns ; i++)
{
col_name = SPI_fname(tupdesc, i);
elog (NOTICE , colname: %s , col_name);
}
SPI_finish();
return PointerGetDatum(rettuple);
}
static int
get_no_columns(char *table_name,char *table_nspname )
{
char query[512];
int ret;
int ncolumns = -1;
snprintf(query, 511,
SELECT COUNT(pg_attribute.attname) AS a FROM
pg_class, pg_attribute , pg_namespace WHERE pg_class.relname='%s' and
pg_namespace.nspname='%s' AND pg_attribute.attnum 0 AND
pg_attribute.attrelid=pg_class.oid and
pg_class.relnamespace=pg_namespace.oid and attisdropped is false ,
table_name,
table_nspname
);
if ((ret = SPI_exec(query, 0)) 0)
{
elog(ERROR, get_no_columnss: could not get number of columns from
relation %s.%s ret: %d,
table_nspname , table_name , ret );
}
if (SPI_processed 0)
{
/* this is a old code which is not using DatumGetInt64 , sorry for that :(
if its a mistake. */
ncolumns =
DatumGetInt32(DirectFunctionCall1
(int4in,
CStringGetDatum(SPI_getvalue
(SPI_tuptable-
vals[0], SPI_tuptable-tupdesc, 1;
if (ncolumns 1)
{
elog(ERROR, get_no_columns: relation %s.%s does not exist,
table_nspname, table_name);
}
}
else
{
elog(ERROR,
get_no_columns: could not get number columns in relation %s.%s,
table_nspname , table_name);
}
return (ncolumns);
}
-- end of code
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Proper way of iterating over the column names in a trigger function. [ SOLVED]
On 12/6/06, Tom Lane [EMAIL PROTECTED] wrote:
Rajesh Kumar Mallah [EMAIL PROTECTED] writes:
what is the proper way for iterating over column names of a table using
SPI_* functions.
You need to pay attention to the attisdropped field of the TupleDesc
entries.
thanks.
did the below (hopefully gotcha free)
for (i = 1; i = tupdesc-natts ; i++)
{
if ( tupdesc-attrs[i-1]-attisdropped)
continue;
col_name = SPI_fname(tupdesc, i);
elog (NOTICE , colname: %s , col_name);
}
Warm Regds
Mallah.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] calling elog possibly causing problem in DirectFunctionCall1
Hi ,
In certain C trigger function following code snippet causes ERROR:
---
elog (NOTICE , before calling DirectFunctionCall1);
data-time_stamp =
DirectFunctionCall1(timestamptz_in, CStringGetDatum(now));
elog (NOTICE , after calling DirectFunctionCall1);
begin work;INSERT INTO audittest.test_table (name,foo) values
('test','test'); rollback;
BEGIN
NOTICE: before calling DirectFunctionCall1
ERROR: timestamp(-1073748880) precision must be between 0 and 6
ROLLBACK
If the elog before DirectFunctionCall1 is removed the code works fine.
begin work;INSERT INTO audittest.test_table (name,foo) values
('test','test'); rollback;
BEGIN
NOTICE: after calling DirectFunctionCall1
INSERT 0 1
ROLLBACK
Can anyone please explain how removing a elog makes a difference?
Regds
Mallah.
Re: [SQL] calling elog possibly causing problem in DirectFunctionCall1
On 12/1/06, Tom Lane [EMAIL PROTECTED] wrote: Rajesh Kumar Mallah [EMAIL PROTECTED] writes: data-time_stamp = DirectFunctionCall1(timestamptz_in, CStringGetDatum(now)); This code is incorrect, as timestamptz_in takes three arguments. Dear Sir, thanks for the kind reply. field time_stamp of data is declared as: Datum time_stamp; it is supposed to be populated with current timestamp , (now()) can you please tell me what should be passed as the third argument? Regds Mallah. regards, tom lane
Re: [SQL] calling elog possibly causing problem in DirectFunctionCall1
On 12/1/06, Tom Lane [EMAIL PROTECTED] wrote: Rajesh Kumar Mallah [EMAIL PROTECTED] writes: data-time_stamp = DirectFunctionCall1(timestamptz_in, CStringGetDatum(now)); This code is incorrect, as timestamptz_in takes three arguments. replaced it with: data-time_stamp = DirectFunctionCall3(timestamptz_in, CStringGetDatum(now), ObjectIdGetDatum(InvalidOid), Int32GetDatum(-1)) now it works fine. (code lifted from contrib/spi/moddatetime.c(line 73) hopefully its correct. regards, tom lane
[SQL] transactional shared variable in postgresql
Hi , Is there any way to set a variable from a web application (using dbi/perl , libpq etc), and access the same variable from a C trigger inside a transaction ? the %_SHARED hash available in plperl provides only session level isolation and does not suit the requirement. Original problem: we want to setup table auditing using table_log or audittrail projects. the triggers used in these systems uses the current database user to log to the audit tables.. In our webapps, we use the same username to connect to the database for all kind of updates. Hence we are not able to makeout whoo modified what . However at application level we have different userid for different users of the system. we want to somehow pass this userid to the databasee server and accesss it from the triggers that implement the audit functions. we thought of using a table of single row and single column to store the userid but we are concerned over the performance due to locking issues. Regds Mallah.
Re: [SQL] transactional shared variable in postgresql
hi, We do not want to modify our apps for doing auditing. we are considering table level auditing auditrail http://gborg.postgresql.org/project/audittrail/projdisplay.php seems to be doing a good job. i just need to access the username that starts the transaction in webapp from the trigger in audittrail. basically we want to log selected tables of database without modifying our web application code. is it possible to get transaction id (XID) for current transaction ? should it be used ? sorry for ignorance but i read some discouraging remarks regarding using it in apps as it is an internal stuff. regds mallah. ---(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] transactional shared variable in postgresql
On 11/18/06, Volkan YAZICI [EMAIL PROTECTED] wrote: Hi, On Nov 18 06:00, Rajesh Kumar Mallah wrote: Is there any way to set a variable from a web application (using dbi/perl , libpq etc), and access the same variable from a C trigger inside a transaction ? Why don't you use a temporary table for that purpose? sounds good, what i understand you mean to say that i create a temp table (with on commit drop option) insert my value there and read the value from inside the trigger. i do not know about the performance aspect of this approach though. i was thinking of a method that could be performed everytime my webapp connected to database. regds mallah. Regards. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Random()
On 11/18/06, A. Kretschmer [EMAIL PROTECTED] wrote: am Sat, dem 18.11.2006, um 23:02:33 +0530 mailte Rajesh Kumar Mallah folgendes: select bigint_column from your_table order by random() limit 1; This query will tend to get slower as the table grows because of the sorting. Right. it possible to get a row from a random offset how about select bigint_column from your_table limit 1 offset 2*random() ; For instance, you have a table with only 500 rows and random() returns 0.999? In other words: in think, this is a bad idea, unless you use a constant for the offset-calculation thats not larger than the number of rows in this table. For this, you need this value, but select count(1) from table force a seqscan - slow. but order by random() would also forces a seqscan , looks like the random offset method performs better explain analyze select count(*) from general.profile_master; ++ | QUERY PLAN | ++ | Aggregate (cost=139214.26..139214.27 rows=1 width=0) (actual time= 3071.178..3071.179 rows=1 loops=1) | | - Seq Scan on profile_master (cost=0.00..137703.21 rows=604421 width=0) (actual time=0.032..2686.842 rows=601240 loops=1) | | Total runtime: 3071.268ms | ++ (3 rows) tradein_clients= explain analyze SELECT profile_id from general.profile_master limit 1 offset 601240*random(); ++ | QUERY PLAN | ++ | Limit (cost=13770.30..13770.53 rows=1 width=4) (actual time= 1614.146..1614.147 rows=1 loops=1)| | - Seq Scan on profile_master (cost=0.00..137703.21 rows=604421 width=4) (actual time=0.036..1375.742 rows=429779 loops=1) | | Total runtime: 1614.187ms | ++ (3 rows) tradein_clients= explain analyze SELECT profile_id from general.profile_master order by random() limit 1; +--+ | QUERY PLAN | +--+ | Limit (cost=207079.39..207079.40 rows=1 width=4) (actual time= 11715.694..11715.695 rows=1 loops=1) | | - Sort (cost=207079.39..208590.45 rows=604421 width=4) (actual time= 11715.691..11715.691 rows=1 loops=1)| | Sort Key: random() | | - Seq Scan on profile_master (cost=0.00..139214.26 rows=604421 width=4) (actual time=0.036..4605.259 rows=601241 loops=1) | | Total runtime: 11716.039ms | +--+ (5 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(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] transactional shared variable in postgresql
On 11/19/06, Tom Lane [EMAIL PROTECTED] wrote: Rajesh Kumar Mallah [EMAIL PROTECTED] writes: In our webapps, we use the same username to connect to the database for all kind of updates. Hence we are not able to makeout whoo modified what . However at application level we have different userid for different users of the system. we want to somehow pass this userid to the databasee server and accesss it from the triggers that implement the audit functions. Perhaps it would be better to make your userid be the actual database user? But anyway, the custom GUC variable facility might serve your Hi, wouldnt' it affect the connection pooling , i am using DBI::Cache and mod_perl ? needs. http://www.postgresql.org/docs/8.1/static/runtime-config-custom.html i dont think i understand the usage properly, i did the below postgresql.conf custom_variable_classes = 'general' # list of custom variable class names general.employee = '' am i supposed to see the variable general.employee when i do show all ? i did not see it in fact! my question is how can i set it and read it regds Mallah. regards, tom lane
Re: [SQL] converting epoch to timestamp
On 10/26/05, Richard Huxton dev@archonet.com wrote: Rajesh Kumar Mallah wrote: Hi, Can anyone tell me how to convert epoch to timestamp ? ie reverse of : SELECT EXTRACT( epoch FROM now() ); I'd start with either Google or the manuals. http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html Firstly Thanks everyone for the response. I did read this document but not too carefully hence missed. I missed because i was not careful and partly because i was not expecting that little note to be under documentation of EXTRACT which deals with getting date/time sub fields. I am no documentation expert just trying to explain why i could not find it. PS: sorry for late reply Regds Mallah. Scroll down to the section on epoch here and see the example. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] converting epoch to timestamp
Hi, Can anyone tell me how to convert epoch to timestamp ? ie reverse of : SELECT EXTRACT( epoch FROM now() ); +--+ |date_part | +--+ | 1130317518.61997 | +--+ (1 row) Regds mallah. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Help with a view
Basically, what I've got is the first person and the tag_type. I can do
it with a function from PHP:
function get_spouses($p) {
$handle = pg_query(select person from principals
where event in (select event from principals
where person = $p and tag_type = .MARR. )
order by sort_date);
What about adding an extra condition ' and person $p ?
select person from principals as a
where event in (select event from principals as b
where person = $p and tag_type = .MARR. )
and a.person $p
order by sort_date
pgslekt= select * from principals where event=15821;
person | event | place | sort_date | tag_type
+---+---++--
2 | 15821 | 1152 | 1999-09-17 |4
3 | 15821 | 1152 | 1999-09-17 |4
(2 rows)
I'd also like a view that gives me the two rows combined into one, with
person replaced by p1 and p2.
might consider grouping by all other columns except person and use an
aggregate function over person ,
CREATE VIEW blah AS select list(person) as persons , event , place ,
sort_date , tag_type from principals group by event , place ,
sort_date , tag_type ;
(list is a custom function in my database , currently not posted)
not sure though if i got your problem exactly.
regds
mallah.
$i=0;
$spouses = array();
while ($row = pg_fetch_row($handle) {
if $row[0] != $p
$spouses[$i++] = $row[0];
}
return $spouses;
}
---(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] Issue with UPDATE statement on v8
On 9/23/05, Kenneth Hutchinson [EMAIL PROTECTED] wrote: Hello, I'm sorry if this has been sent to the community multiple times. I am not able to determine whether my posts have gotten through. If you have rec'd this multiple times, please let me know. We have recently migrated to Postgres 8 (not sure of exactly which build). We have noticed that a few functions that were working previously are no longer behaving as expected. One function in particular is giving me a strange result. The function giving us the problem is much more complicated, but for simplicity I've included one that is easier to read and results in the same behavior. UPDATE t_summary SETavailability = 7 WHERE oid = 28245084 When this query is executed (within a function or without) the database will simply hang. see if the update statement is being blocked by some other statement by running following SQL stmt from another session while the first session is hung SELECT h.pid AS blocker, w.pid AS blockee FROM ONLY pg_locks h, ONLY pg_locks w WHERE h.granted AND NOT w.granted AND (h.relation = w.relation AND h.database = w.database OR h.transaction = w.transaction); (sql above was posted by mr. Tom Lane in a particular reply) But You have to enable command string in statictics part of postgresql.conf file to know which pid corresponds to which sql. http://www.postgresql.org/docs/8.0/interactive/monitoring-stats.html#MONITORING-STATS-SETUP Hope it helps regds mallah. If the UPDATE is turned into a SELECT, the query works just fine. For some reason, the UPDATE is just not working. This same function/query works fines in Postgres 7.2. The schema for the targeted table is shown below. CREATE TABLE t_summary ( id varchar(20) NULL, availability int4 NULL DEFAULT 0, ) Has anyone else experienced a similar issue? If more information is needed to determine the problem, please let me know. I've trimmed down the function's query and table's schema for this posting. Thanks in advance! kh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] partial index on non default tablespace syntax
Fuhr,Tom and Everyone Extremely sorry for not consulting the docs. I was doing this: CREATE INDEX foo_name_idx ON foo (name) WHERE name IS NOT NULL TABLESPACE testspace ; Regds Rajesh Kumar Mallah. On 6/18/05, Michael Fuhr [EMAIL PROTECTED] wrote: On Sat, Jun 18, 2005 at 10:24:06PM +0530, Rajesh Kumar Mallah wrote: Looks like its not possible to specify tablespace of an index with a where clause, Could you show the command you're running and the error you get, or otherwise explain what problem you're seeing? Is the following not what you're looking for? CREATE INDEX foo_name_idx ON foo (name) TABLESPACE testspace WHERE name IS NOT NULL; http://www.postgresql.org/docs/8.0/static/sql-createindex.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] partial index on non default tablespace syntax
Hi, Looks like its not possible to specify tablespace of an index with a where clause, we require to create the index, and then use ALTER INDEX for setting the tablespace. Is it something that is already known or its itentional so as to keep the command unambigious. i also could not find it in the TODO Regds Rajesh Kumar Mallah. ---(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] plpgsql dynamic record access
Ganesh, Did you have a look at example Example 35-2. A PL/pgSQL http://www.postgresql.org/docs/current/static/plpgsql-trigger.html Regds maLLAH ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Tsearch2 headline usage
Hi There,
We are using tsearch2 for FTS implementation. For highlighting the search term
in the result we are displaying the output of headline function which is
supposed to tag (mark up) those stemmed words in the text that match any of the
stemmed words in search term. The problem is that some initial part of the text
is being truncated from the headline output. Can anyone please suggest on how
to overcome this phenomenon.
tradein_clients= SELECT headline( 'A
Leading manufacturer Exporter of TAJ Brand Ice cream Plant
Dairy Equipments with complete plants in various capacities.
Pasteuriser, Homogenizer, Surface Cooler, Plate Chiller, Ageing Vat,
Ice Cream Continuous Freezer, Single Flavour Softy, Bulk Milk Coolers.'
, to_tsquery('icecreamplant') , 'StartSel=b,
StopSel=/b' ) ;
headline
bcream/b bPlant/b Dairy
Equipments with complete bplants/b in various
capacities. Pasteuriser, Homogenizer, Surface Cooler, Plate Chiller,
Ageing Vat, bIce/b bCream/b
(1 row)
Regds
Mallah.
[SQL] Arbitrary precision arithmatic with pgsql
Hi, The docs says that numeric type supports numbers upto any precision docs 8.1.2. Arbitrary Precision Numbers The type numeric can store numbers with up to 1000 digits of precision and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required. However, the numeric type is very slow compared to the floating-point types described in the next section. /docs However tradein_clients=# SELECT cast(2^100 as numeric); +-+ | numeric | +-+ | 126765060022823 | +-+ (1 row) Time: 1036.063 ms Naturally there is a loss of information here. So my question is 1. Does the specs not require pgsql to print a warning or info , will it not be considered silient truncation of data. 2. Is there any way to do such calculation using pgsql, i understand bc is a better tool for it. Warm Regards Rajesh Kumar Mallah. -- regds Mallah. Rajesh Kumar Mallah +---+ | Tradeindia.com (3,11,246) Registered Users | | Indias' Leading B2B eMarketPlace | | http://www.tradeindia.com/ | +---+ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[SQL] Comparing tsearch2 vectors.
Hi,
We want to compare strings after stemming. Can anyone
tell me what is the best method. I was thinking to compare
the tsvector ,but there is no operator for that.
Regds
Mallah.
tradein_clients=# SELECT to_tsvector('handicraft exporters');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 710.315 ms
tradein_clients=#
tradein_clients=# SELECT to_tsvector('handicrafts exporter');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 400.679 ms
tradein_clients=# SELECT to_tsvector('Hi there') = to_tsvector('Hi there');
ERROR: operator does not exist: tsvector = tsvector
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
tradein_clients=#
--
regds
Mallah.
Rajesh Kumar Mallah
+---+
| Tradeindia.com (3,11,246) Registered Users |
| Indias' Leading B2B eMarketPlace |
| http://www.tradeindia.com/ |
+---+
---(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] Comparing tsearch2 vectors.
Dear Mantzios,
I have to get set of banners from database in
response to a search term. I want that the search term
be compared to the keyword corresponding to the
banners stored in database. current i am doing an
equality match but i woild like to do it after stemming
both the sides (serch term and keywords).
So that the banners for the adword say 'incense exporter' is
shown even if 'incenses exporter' or 'incense exporters' is
searched.
I hope i am able to clarify.
Regds
Mallah.
Achilleus Mantzios wrote:
O kyrios Rajesh Kumar Mallah egrapse stis Jul 12, 2004 :
Hi,
We want to compare strings after stemming. Can anyone
tell me what is the best method. I was thinking to compare
the tsvector ,but there is no operator for that.
I'd tokenize each string and then apply lexize() to get the
equivalent stemified
word, but what exactly are you trying to accomplish?
Regds
Mallah.
tradein_clients=# SELECT to_tsvector('handicraft exporters');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 710.315 ms
tradein_clients=#
tradein_clients=# SELECT to_tsvector('handicrafts exporter');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 400.679 ms
tradein_clients=# SELECT to_tsvector('Hi there') = to_tsvector('Hi there');
ERROR: operator does not exist: tsvector = tsvector
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
tradein_clients=#
--
regds
Mallah.
Rajesh Kumar Mallah
+---+
| Tradeindia.com (3,11,246) Registered Users |
| Indias' Leading B2B eMarketPlace |
| http://www.tradeindia.com/ |
+---+
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Comparing tsearch2 vectors.
Achilleus Mantzios wrote:
O kyrios Rajesh Kumar Mallah egrapse stis Jul 12, 2004 :
Dear Mantzios,
I have to get set of banners from database in
response to a search term. I want that the search term
be compared to the keyword corresponding to the
banners stored in database. current i am doing an
equality match but i woild like to do it after stemming
both the sides (serch term and keywords).
You could transform your search terms so that there is the
separator between them. ( stands for AND).
E.g. handicrafts exporter becomes handicraftsexporter
And then
select * from your table where idxfti @@ to_tsquery(searchterms);
But i do not want 'handicraft exporters of delhi' to pop out if i search
for 'handicrafts exporters' whereas
SELECT to_tsvector('handycrafts exporters of delhi') @@
to_tsquery('handycraftexporting');
will be true.
Regds
Mallah.
where idxfti is your tsvector column.
E.g.
# SELECT to_tsvector('handycrafts exporters') @@ to_tsquery('handycraftexporting');
?column?
--
t
(1 row)
So that the banners for the adword say 'incense exporter' is
shown even if 'incenses exporter' or 'incense exporters' is
searched.
I hope i am able to clarify.
Regds
Mallah.
Achilleus Mantzios wrote:
O kyrios Rajesh Kumar Mallah egrapse stis Jul 12, 2004 :
Hi,
We want to compare strings after stemming. Can anyone
tell me what is the best method. I was thinking to compare
the tsvector ,but there is no operator for that.
I'd tokenize each string and then apply lexize() to get the
equivalent stemified
word, but what exactly are you trying to accomplish?
Regds
Mallah.
tradein_clients=# SELECT to_tsvector('handicraft exporters');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 710.315 ms
tradein_clients=#
tradein_clients=# SELECT to_tsvector('handicrafts exporter');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 400.679 ms
tradein_clients=# SELECT to_tsvector('Hi there') = to_tsvector('Hi there');
ERROR: operator does not exist: tsvector = tsvector
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
tradein_clients=#
--
regds
Mallah.
Rajesh Kumar Mallah
+---+
| Tradeindia.com (3,11,246) Registered Users |
| Indias' Leading B2B eMarketPlace |
| http://www.tradeindia.com/ |
+---+
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Secure DB Systems - How to
Sarah Tanembaum wrote: I was wondering if it is possible to create a secure database system usingPostgreSQL/PHP combination? I have the following in mind: I wanted to store all my( and my brothers and sisters) important document information such as birth certificate, SSN, passport number, travel documents, insurance(car, home, etc) document, and other important documents imagined in the database. The data will be entered either manually and/or scanned(with OCR). I need to be able to search on all the fields in the database. We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I maintained. The data should be synchronize/replicate between those computers. Well, so far it is easy, isn't it? Here's my question: a) How can I make sure that it secure so only authorized person can modify/add/delete the information? Beside transaction logs, are there any other method to trace any transaction(kind of paper trail)? There can be multiple solutions to your problem. The security and logging may be implemented either at database level or application level. That is a call you have to take. If you consider the database to take care of security and logging you could do the following. 1. create a database user for each of your family members 2. ask the memebers to login to your application using their own id. 3. Use that id for connecting to the database using php. the security at table level can be managed by various GRANT commands. the security at row level can be done using a mechanism methods describe in the -general mailling list (search: row level grants). For logging changes to your tables you can create audit trail of all the tables in question by using triggers or enbale logging of sql statements (with current user display) in postgresql server. u may consider: http://gborg.postgresql.org/project/audittrail/projdisplay.php although i have not used it myself. Assuming there are 3 step process to one enter the info e.g: - One who enter the info (me) - One who verify the info(the owner of info) - One who verify and then commit the change! How can I implement such a process in PostgreSQL and/or PHP or any other web language? I think such a moderation should be implemented at application level. b) How can I make sure that no one can tap the info while we are entering the data in the computer? (our family are scattered within US and Canada) you may run yor web application using https:// rather than http:// and you may enable ssl in postgresql for securing the communication between application and database. c) Is it possible to securely synchronize/replicate between our computers using VPN? Does PostgreSQL has this functionality by default? Slony and many other replication solution exists for asyncronous replication. Hope it helps a bit. Regds Mallah. d) Other secure method that I have not yet mentioned. Anyone has good ideas on how to implement such a systems? Thanks ---(end of broadcast)--- TIP 8: explain analyze is your friend -- regds Mallah. Rajesh Kumar Mallah +---+ | Tradeindia.com (3,11,246) Registered Users | | Indias' Leading B2B eMarketPlace | | http://www.tradeindia.com/ | +---+ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Comparing tsearch2 vectors.
Achilleus Mantzios wrote:
O kyrios Rajesh Kumar Mallah egrapse stis Jul 12, 2004 :
Achilleus Mantzios wrote:
O kyrios Rajesh Kumar Mallah egrapse stis Jul 12, 2004 :
Dear Mantzios,
I have to get set of banners from database in
response to a search term. I want that the search term
be compared to the keyword corresponding to the
banners stored in database. current i am doing an
equality match but i woild like to do it after stemming
both the sides (serch term and keywords).
You could transform your search terms so that there is the
separator between them. ( stands for AND).
E.g. handicrafts exporter becomes handicraftsexporter
And then
select * from your table where idxfti @@ to_tsquery(searchterms);
But i do not want 'handicraft exporters of delhi' to pop out if i search
for 'handicrafts exporters' whereas
SELECT to_tsvector('handycrafts exporters of delhi') @@
to_tsquery('handycraftexporting');
will be true.
Define what you want, and then read tsearch2 userguide.
I'm sure you'll find your way :)
The requirement is different than full text search.
I am not searching a word in a collection of words (text)
rather comparing two strings after all the words in those
strings are stemmed. Hope my requirement is clear now.
Regds
mallah.
Regds
Mallah.
where idxfti is your tsvector column.
E.g.
# SELECT to_tsvector('handycrafts exporters') @@ to_tsquery('handycraftexporting');
?column?
--
t
(1 row)
So that the banners for the adword say 'incense exporter' is
shown even if 'incenses exporter' or 'incense exporters' is
searched.
I hope i am able to clarify.
Regds
Mallah.
Achilleus Mantzios wrote:
O kyrios Rajesh Kumar Mallah egrapse stis Jul 12, 2004 :
Hi,
We want to compare strings after stemming. Can anyone
tell me what is the best method. I was thinking to compare
the tsvector ,but there is no operator for that.
I'd tokenize each string and then apply lexize() to get the
equivalent stemified
word, but what exactly are you trying to accomplish?
Regds
Mallah.
tradein_clients=# SELECT to_tsvector('handicraft exporters');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 710.315 ms
tradein_clients=#
tradein_clients=# SELECT to_tsvector('handicrafts exporter');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 400.679 ms
tradein_clients=# SELECT to_tsvector('Hi there') = to_tsvector('Hi there');
ERROR: operator does not exist: tsvector = tsvector
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
tradein_clients=#
--
regds
Mallah.
Rajesh Kumar Mallah
+---+
| Tradeindia.com (3,11,246) Registered Users |
| Indias' Leading B2B eMarketPlace |
| http://www.tradeindia.com/ |
+---+
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Problem in Stored Procedures
Pradeepkumar, Pyatalo (IE10) wrote: Hi all, I m using Postgresql version 7.1.3-2. The create type and create function does work in PostgreSQL 7.4 without any modification. I guess the features you are trying to use are not supported in 7.1.x Regds Mallah. I have written a function which accepts 2 arguments and returns matching tuples from a table based on the arguments passed...but i am having problems in getting it work. This is my function - CREATE TYPE PointType AS(ParamId INTEGER,ParamName VARCHAR(5),Is_FixEnum BIT,Is_ExpandEnum BIT); CREATE FUNCTION PP_ReadParameter(integer,varchar) RETURNS setof PointType AS ' DECLARE rec PointType; BEGIN IF $1 IS NOT NULL THEN FOR rec IN SELECT ParamId, ParamName, Is_FixEnum, Is_ExpandEnum AttributeId, AttributeName,IsFixEnum,IsExpandEnum FROM Attributes WHERE AttributeId = $1 ORDER BY AttributeId LOOP RETURN NEXT rec; END LOOP; RETURN; END IF; ELSE IF $2 IS NOT NULL THEN FOR rec IN SELECT ParamId, ParamName, Is_FixEnum, Is_ExpandEnum AttributeId, AttributeName,IsFixEnum,IsExpandEnum FROM Attributes WHERE AttributeId = $2 ORDER BY AttributeId LOOP RETURN NEXT rec; END LOOP; RETURN; ELSE FOR rec IN SELECT ParamId, ParamName, Is_FixEnum, Is_ExpandEnum AttributeId, AttributeName,IsFixEnum,IsExpandEnum FROM Attributes ORDER BY AttributeId LOOP RETURN NEXT rec; END LOOP; RETURN; END IF; END IF; END; ' language 'plpgsql'; I get the error... psql:Procedures.sql:2: ERROR: parse error at or near AS (for CREATE TYPE command) psql:Procedures.sql:40: NOTICE: return type 'pointtype' is only a shell CREATE WHEN I EXECUTE THE FUNCTION USING SELECT (pp_readparameter(42,null)); ERROR: fmgr_info: function 0: cache lookup failed. any value inputs on why this is happening. With Best Regards Pradeep Kumar P J ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- regds Mallah. Rajesh Kumar Mallah +---+ | Tradeindia.com (3,11,246) Registered Users | | Indias' Leading B2B eMarketPlace | | http://www.tradeindia.com/ | +---+ ---(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] Query becoming slower on adding a primary key
Hi, Is there any solution to this issue ? I am facing it every week. Warm Regds Mallah. Rajesh Kumar Mallah wrote: Tom Lane wrote: [EMAIL PROTECTED] writes: tradein_clients=# explain analyze select email_id ,email ,contact from t_a a join email_source f using(email_id) join email_subscriptions h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ; Runs for Ever. So what does plain explain say about it? Oops sorry that was a valuable info i left. (sorry for delay too) tradein_clients=# explain select email_id ,email ,contact from t_a a join email_source f using(email_id) join email_subscriptions h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ; +-+ | QUERY PLAN| +-+ | Hash Join (cost=133741.48..224746.39 rows=328814 width=40) | | Hash Cond: (outer.email_id = inner.email_id) | | - Seq Scan on email_subscriptions h (cost=0.00..70329.54 rows=749735 width=4) | | Filter: (sub_id = 3)| | - Hash (cost=130230.99..130230.99 rows=324994 width=44)| | - Hash Join (cost=26878.00..130230.99 rows=324994 width=44) | | Hash Cond: (outer.email_id = inner.email_id) | | - Seq Scan on email_source f (cost=0.00..26159.21 rows=324994 width=4) | | Filter: (source_id = 1) | | - Hash (cost=18626.80..18626.80 rows=800080 width=40) | | - Seq Scan on t_a a (cost=0.00..18626.80 rows=800080 width=40) | +-+ (11 rows) Time: 452.417 ms tradein_clients=# ALTER TABLE t_a add primary key(email_id); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index t_a_pkey for table t_a ALTER TABLE Time: 7923.230 ms tradein_clients=# explain select email_id ,email ,contact from t_a a join email_source f using(email_id) join email_subscriptions h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ; +---+ |QUERY PLAN | +---+ | Hash Join (cost=106819.76..197824.68 rows=328814 width=40) | | Hash Cond: (outer.email_id = inner.email_id) | | - Seq Scan on email_subscriptions h (cost=0.00..70329.54 rows=749735 width=4)| | Filter: (sub_id = 3) | | - Hash (cost=103309.28..103309.28 rows=324994 width=44) | | - Merge Join (cost=0.00..103309.28 rows=324994 width=44) | | Merge Cond: (outer.email_id = inner.email_id) | | - Index Scan using t_a_pkey on t_a a (cost=0.00..44689.59 rows=800080 width=40) | | - Index Scan using email_source_pkey on email_source f (cost=0.00..52602.59 rows=324994 width=4) | | Filter: (source_id = 1) | +---+ (10 rows) Time: 2436.551 ms tradein_clients=# Regds Mallah. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Query becoming slower on adding a primary key
Tom Lane wrote:
[EMAIL PROTECTED] writes:
tradein_clients=# explain analyze select email_id ,email ,contact from
t_a a join email_source f using(email_id) join email_subscriptions h
using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
Runs for Ever.
So what does plain explain say about it?
Oops sorry that was a valuable info i left. (sorry for delay too)
tradein_clients=# explain select email_id ,email ,contact from
t_a a join email_source f using(email_id) join email_subscriptions h
using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
+-+
| QUERY
PLAN |
+-+
| Hash Join (cost=133741.48..224746.39 rows=328814
width=40) |
| Hash Cond: ("outer".email_id =
"inner".email_id) |
| - Seq Scan on email_subscriptions h (cost=0.00..70329.54
rows=749735 width=4) |
| Filter: (sub_id =
3) |
| - Hash (cost=130230.99..130230.99 rows=324994
width=44) |
| - Hash Join (cost=26878.00..130230.99 rows=324994
width=44) |
| Hash Cond: ("outer".email_id =
"inner".email_id) |
| - Seq Scan on email_source f (cost=0.00..26159.21
rows=324994 width=4) |
| Filter: (source_id =
1) |
| - Hash (cost=18626.80..18626.80 rows=800080
width=40) |
| - Seq Scan on t_a a (cost=0.00..18626.80
rows=800080 width=40) |
+-+
(11 rows)
Time: 452.417 ms
tradein_clients=# ALTER TABLE t_a add primary key(email_id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"t_a_pkey" for table "t_a"
ALTER TABLE
Time: 7923.230 ms
tradein_clients=# explain select email_id ,email ,contact from t_a a
join email_source f using(email_id) join email_subscriptions
h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
+---+
| QUERY
PLAN |
+---+
| Hash Join (cost=106819.76..197824.68 rows=328814
width=40) |
| Hash Cond: ("outer".email_id =
"inner".email_id)
|
| - Seq Scan on email_subscriptions h (cost=0.00..70329.54
rows=749735 width=4) |
| Filter: (sub_id =
3)
|
| - Hash (cost=103309.28..103309.28 rows=324994
width=44) |
| - Merge Join (cost=0.00..103309.28 rows=324994
width=44) |
| Merge Cond: ("outer".email_id =
"inner".email_id) |
| - Index Scan using t_a_pkey on t_a a
(cost=0.00..44689.59 rows=800080 width=40) |
| - Index Scan using email_source_pkey on
email_source f (cost=0.00..52602.59 rows=324994 width=4) |
| Filter: (source_id =
1)
|
+---+
(10 rows)
Time: 2436.551 ms
tradein_clients=#
Regds
Mallah.
regards, tom lane
Re: [SQL] assistance on self join pls
Dear Darren, Your question is not very clear to me. On what columns do you want to aggregate? suppose u want to aggregate on outsite and inside ip you shud group by those columns and run a aggregate function like sum or avg etc , suppose u want the total traffic for every pair you can do this: select inside_ip,outside_ip , sum(outbound_bytes) as total_traffic from connection_data group by inside_ip,outside_ip ; Hope it helps. Regds Mallah. email lists wrote: Hi all, I have the following firewall connection data. datetime | protocol | port | inside_ip| outside_ip | outbound_count | outbound_bytes -+--+---++-- --++--- 2004-05-05 05:00:00 |6 |21 | 192.168.11.191 | 205.227.137.53 | 6 | 3881 2004-05-05 05:00:00 |6 | 22326 | 192.168.11.191 | 205.227.137.53 | 1 | 2592 2004-05-05 05:00:00 |6 | 38005 | 192.168.11.191 | 205.227.137.53 | 1 | 51286 2004-05-05 05:00:00 |6 | 51861 | 192.168.11.191 | 205.227.137.53 | 1 | 42460 2004-05-05 05:00:00 |6 | 52095 | 192.168.11.191 | 205.227.137.53 | 1 | 2558 2004-05-05 05:00:00 |6 | 59846 | 192.168.11.191 | 205.227.137.53 | 1 |118 2004-05-05 05:00:00 |6 | 60243 | 192.168.11.191 | 205.227.137.53 | 1 | 2092 2004-05-05 06:00:00 |6 |21 | 192.168.11.185 | 205.227.137.53 | 6 | 3814 2004-05-05 06:00:00 |6 | 29799 | 192.168.11.185 | 205.227.137.53 | 1 |118 2004-05-05 06:00:00 |6 | 30138 | 192.168.11.185 | 205.227.137.53 | 1 | 2092 2004-05-05 06:00:00 |6 | 30215 | 192.168.11.185 | 205.227.137.53 | 1 | 42460 2004-05-05 06:00:00 |6 | 51279 | 192.168.11.185 | 205.227.137.53 | 1 | 1332 2004-05-05 06:00:00 |6 | 52243 | 192.168.11.185 | 205.227.137.53 | 1 | 51286 2004-05-05 06:00:00 |6 | 60079 | 192.168.11.185 | 205.227.137.53 | 1 | 2558 I am wanting to aggregate / collapse each entry to something similar to: datetime | protocol | port | inside_ip| outside_ip | outbound_count | outbound_bytes -+--+---++-- --++--- 2004-05-05 05:00:00 |6 |21 | 192.168.11.191 | 205.227.137.53 | 12 | 104987 2004-05-05 06:00:00 |6 |21 | 192.168.11.185 | 205.227.137.53 | 12 | 103660 I have not had much success - any assistance greatly appreciated Darren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Query becoming slower on adding a primary key
Even the first query used to run fine before but one fine day it changed plans i think. Regds Mallah. Rajesh Kumar Mallah wrote: Tom Lane wrote: [EMAIL PROTECTED] writes: tradein_clients=# explain analyze select email_id ,email ,contact from t_a a join email_source f using(email_id) join email_subscriptions h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ; Runs for Ever. So what does plain explain say about it? Oops sorry that was a valuable info i left. (sorry for delay too) tradein_clients=# explain select email_id ,email ,contact from t_a a join email_source f using(email_id) join email_subscriptions h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ; +-+ | QUERY PLAN| +-+ | Hash Join (cost=133741.48..224746.39 rows=328814 width=40) | | Hash Cond: (outer.email_id = inner.email_id) | | - Seq Scan on email_subscriptions h (cost=0.00..70329.54 rows=749735 width=4) | | Filter: (sub_id = 3)| | - Hash (cost=130230.99..130230.99 rows=324994 width=44)| | - Hash Join (cost=26878.00..130230.99 rows=324994 width=44) | | Hash Cond: (outer.email_id = inner.email_id) | | - Seq Scan on email_source f (cost=0.00..26159.21 rows=324994 width=4) | | Filter: (source_id = 1) | | - Hash (cost=18626.80..18626.80 rows=800080 width=40) | | - Seq Scan on t_a a (cost=0.00..18626.80 rows=800080 width=40) | +-+ (11 rows) Time: 452.417 ms tradein_clients=# ALTER TABLE t_a add primary key(email_id); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index t_a_pkey for table t_a ALTER TABLE Time: 7923.230 ms tradein_clients=# explain select email_id ,email ,contact from t_a a join email_source f using(email_id) join email_subscriptions h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ; +---+ |QUERY PLAN | +---+ | Hash Join (cost=106819.76..197824.68 rows=328814 width=40) | | Hash Cond: (outer.email_id = inner.email_id) | | - Seq Scan on email_subscriptions h (cost=0.00..70329.54 rows=749735 width=4)| | Filter: (sub_id = 3) | | - Hash (cost=103309.28..103309.28 rows=324994 width=44) | | - Merge Join (cost=0.00..103309.28 rows=324994 width=44) | | Merge Cond: (outer.email_id = inner.email_id) | | - Index Scan using t_a_pkey on t_a a (cost=0.00..44689.59 rows=800080 width=40) | | - Index Scan using email_source_pkey on email_source f (cost=0.00..52602.59 rows=324994 width=4) | | Filter: (source_id = 1) | +---+ (10 rows) Time: 2436.551 ms tradein_clients=# Regds Mallah. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Problem in SQL Trigger
Can you tell us about the postgresql versions in 7.3 and 9.0 also post the actuall error message from postgresql. regds mallah. Ramesh Patel wrote: Hi i have one problem in Trigger. this trigger alread work on Red Hat Linux 7.3 but now i shift to RHL9.0 in RHL 9.0 not working . in this problem in ROUND function. but how to i slove this i dont know. so please help me. This is Function and Trigger. /// Function Start// CREATE FUNCTION add_issue_fun() returns opaque as' BEGIN UPDATE mtrl_mst set balstk_cs = balstk_cs - NEW.issueqty_cs, balstk_mt = round( cast((balstk_mt - NEW.issueqty_mt) as numeric ),4) where mtrl_mst.mtrl_code = NEW.mtrl_code; UPDATE rcpt_detail set consqty_cs=consqty_cs + NEW.issueqty_cs WHERE rcpt_detail.batch_code = NEW.batch_code and rcpt_detail.mtrl_code = NEW.mtrl_code and rcpt_detail.loc_code = NEW.loc_code; return NULL; END;' language 'plpgsql'; /// Function End// ///Trigger Start // CREATE TRIGGER add_issue_trg AFTER INSERT ON issue_detail FOR EACH ROW EXECUTE PROCEDURE add_issue_fun(); /// Trigger End// Thanking Ramesh Patel Computer Dept. Banasdairy, Palanpur ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Logical comparison on Strings
kumar wrote: Dear Friends, Postgres 7.3.2 on Linux 7. I want to compare to columns and get the logical result as follows. C1 is 'YNYNY' . C2 is 'NNYYY'. I want to compare like AND and OR operators. C1 AND C2 should give result like NNYNY. C1 OR C2 should give result like YNYYY. Bit String Types in PostgreSQL may be what you are looking for. btw: what is linux 7? Please shed some light. Thanks Kumar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Querying two databases
hi, its not possible to join cross database tables . you may keep tables in different schemas instead of databases. you may also try contrib/dblink to use tables from different database. Regds mallah. Pallav Kalva wrote: Hi, I am new to postgres and I need to do a query which joins two tables from different databases. Can you please advice me on how to achieve this in postgres. Thanks! Pallav ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Tsearch2 question: getting histogram of the vector elements
Greetings! My original problem is to de duplicate a list of around 0.3 million company names. Since a company name can be potentially (mis)spelt in numerous ways exactmatch obviously wont work. To make the searches faster i am using tsearch. For each company name i want to search other companies whose name is similar to the company in question. Since inclusion of all the vector elements of a given company reduces the chance of matching i am thinking of excluding the high frequency words from the query. Hence i need to find the high frequency elements like say 'consulting' , 'limited' , 'Private' 'Industries' that occur commonly in company names. In my table i have populated the co_name_vec feild as strip(to_tsvector(co_name)) can anyone help me analyzing the co_name_vec for the high frequency words? Also i would like to know alternate / better solution to this problem. Regds Mallah. SAMPLE DATA. +-+--+ | co_name | co_name_vec| +-+--+ | European Trade Partner Consulting | 'trade' 'consult' 'partner' 'european' | | Gulbrandsen Chemicals Pvt. Ltd. | 'ltd' 'pvt' 'chemic' 'gulbrandsen' | | Govt. of Karnataka, Vision Group on Biotechnology | 'govt' 'group' 'vision' 'karnataka' 'biotechnolog' | | Digital Globalsoft Ltd. (A Hewlett Packard Company) | 'ltd' 'digit' 'compani' 'hewlett' 'packard' 'globalsoft' | | Shanon Construction Material Industries | 'materi' 'shanon' 'industri' 'construct' | | singpore india trade rsources company | 'india' 'trade' 'rsourc' 'compani' 'singpor' | | RGV TELECOM CONSULTANTS PVT. LTD. | 'ltd' 'pvt' 'rgv' 'consult' 'telecom'| | avid information search and documents (p) ltd. | 'p' 'ltd' 'avid' 'inform' 'search' 'document'| | Tavant Technologies India (P) Ltd. | 'p' 'ltd' 'india' 'tavant' 'technolog' | | Maschinen Fabrik (India) Pvt. Ltd | 'ltd' 'pvt' 'india' 'fabrik' 'maschinen' | | Manishri Refractories and Ceramics Pvt. Ltd.| 'ltd' 'pvt' 'ceram' 'manishri' 'refractori' | | xavier export import management institute| 'manag' 'export' 'import' 'xavier' 'institut'| | Best InformationTechnology ltd. | 'ltd' 'best' 'informationtechnolog' | | FutureCalls Technology Private Limited | 'limit' 'privat' 'futurecal' 'technolog' | | mak controls and systems pvt ltd| 'ltd' 'mak' 'pvt' 'system' 'control' | | NATIONAL RESEARCH CENTRE FOR CASHEW | 'centr' 'cashew' 'nation' 'research' | | The Madras Aluminium Company Ltd. | 'ltd' 'madra' 'compani' 'aluminium' | | Shriram Institute for Industrial Research | 'shriram' 'industri' 'institut' 'research' | | All India Carpet Trade Fair Committee | 'fair' 'india' 'trade' 'carpet' 'committe' | | Tuff Security Allied Services | 'alli' 'tuff' 'secur' 'servic' | +-+--+ (20 rows) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] array_lower /array_prepend doubt
Greetings!
can anyone explain why
SELECT array_lower(array_prepend(0, ARRAY[1,2,3]), 1);
returns 0 not 1
because
tradein_clients=# SELECT array_prepend(0, ARRAY[1,2,3]);
+---+
| array_prepend |
+---+
| {0,1,2,3} |
+---+
(1 row)
and
tradein_clients=# SELECT array_lower( ARRAY[0,1,2,3],1 );
+-+
| array_lower |
+-+
| 1 |
+-+
(1 row)
Time: 402.614 ms
Regds
Mallah.
---(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] Adding a column to a VIEW which has dependent objects.
Dear PostgreSQL gurus, How do people extend a parent view which has lot of dependent views? The parent view cannot be dropped because that will require recreating a dozen of dependent views. Is there any workaround. Also is there an easy way of dumping the definitions of all the dependent views of a given object. Does information_schema helps here. Regds mallah. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] transaction processing after error in statement
Rod Taylor wrote: be recovered either. When committing a transaction the effects of all operations that did not fail will be made permanent. This is how transaction processing is described in the literature. I would be interested in reading that (URLs please) as I didn't see anything in the spec that was interesting on this topic. 4.8.5 from Framework (part 01) An SQL-transaction (transaction) is a sequence of executions of SQL-statements that is atomic with respect to recovery. That is to say: either the execution result is completely successful, or it has no effect on any SQL-schemas or SQL-data. Although i am not aware of the roots of this discussion but would like to comment at this point . When we work with sequences an aborted transaction does have a permanent effect on the last value of sequence. Is this behaviour not a violation of above defination of transaction ? Regds Mallah. The "execution result is completely successful" could certainly be used to back up PostgreSQLs choice to force a rollback. However, it doesn't differentiate between execution of what the user requested, and execution of recovery procedures on the successful user elements. Irregardless, I wish a commit on a failed transaction would throw an error -- END is good enough for Rollback or Commit. For PostgreSQL to implement this we need Savepoints or nested transactions internally since in many cases data is physically written in order to perform things like Foreign Key constraint checks. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Joined deletes but one table being a subquery.
Hi Folks, DELETE from eyp_listing where userid=t_a.userid and category_id=t_a.category_id; such queries work perfectly. but if t_a is a subquery how to accomplish the delete. Regds Mallah. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] ALTER TABLE ... DROP CONSTRAINT
if the constraint are named $1 $2 etc you will need to quote them eg ALTER TABLE xyz DROP CONSTRAINT $1 ; in some version you may require ALTER TABLE xyz DROP CONSTRAINT $1 RESTRICT; What is ur version btw? try to post the table structure also. regds mallah. Elielson Fontanezi wrote: Hi all! Who can tell me what postgres version supports ALTER TABLE... DROP CONSTRAINT without the need of droping the table to remove a simple coinstraint. (link) \\\!/ 55 11 5080 9283 !__! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst ---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0 ---()--( ) \ () / \_/\_/ ---(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] relevance
On Thursday 17 Jul 2003 8:21 am, Terence Kearns wrote: select id from tablename where message like '%sql%'; If there any way to determine exactly how many times 'sql' is matched in that search in each particular row, and then sort by the most matches, or am I going to have to write a script to do the sorting for me? You could probably write a function in postgres (say, matchcount()) which returns the match count (possibly using perl and a regex). Why reinvent the wheel when tsearch already does the job perfectly and is PostgreSQL compaitable. Regds Mallah. SELECT matchcount(message,'sql') AS matchcount, id FROM tablename WHERE message LIKE '%sql%' ORDER BY matchcount(message,'sql') DESC The ORDER BY will probably fail, but you can try :) ---(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] Blobs
Sir Devi, This is PostgreSQL mailing List. If you need help porting applications from Oracle9i to PostgreSQL we might help. Or if you have generic SQL question not specific to any database we can also consider. for help in Oracle specific problems there may be more appropriate lists on the net. Rajesh Mallah. On Thursday 10 Jul 2003 4:40 pm, sri devi wrote: hi we have to download url files in to oracle using BLOBs how to create oracle table stucture how to write the query,we are using oracle9i,and javaswings,reply me to this id. [EMAIL PROTECTED] thanking you sridevi SMS using the Yahoo! Messenger;Download latest version. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] trigger proceedures in sql
Yes of course! contrib/dbmirror does execute a procedure written in 'C' called recordchange() ON update , insert , delete. If you need help in getting its source lemme know. regds Mallah. On Thursday 10 Jul 2003 11:10 am, adivi wrote: hi, can trigger proceedures ( procedures to be executed from within a trigger ) not be written in sql. i was looking for examples and can find proceedures in 'c' only. regards -adivi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Recursive request ...
Dear Bournon, There are already good implementation of Tree structures in databases ranging from using pure SQL to PostgreSQL specfic methods , less point in revinting wheel unless u really need. Some Pointers: Tree-structure functions http://www.brasileiro.net:8080/postgres/cookbook/ Gist Based: contrib/ltree Joe Celko's Article on Nested Sets Adjacency Lists http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html http://www.google.com/search?hl=enie=UTF-8oe=UTF-8q=adjacency+list+%2B+tree+%2B+joe On Wednesday 09 Jul 2003 8:57 pm, Benoît Bournon wrote: I have to make a function that returns a tree with title and link of a table. Recursively, a information depends on a parent information. It is to organise a menu with parent dependance. How is it possible and faster ? in C ? pl/pgsql or other ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] help yourself by helping others
dear ali,
something like
select machine,date_part('day' , date) , sum(withdrawals) from
Table where date_part('month' , date)='month in question' group by
machine,date_part('day' , date) ;
will give you agrregated withdrawals by machine and day,
use the frontend language for formatting it.
note that it will not display the days for which there
has been no withdrawls. If u need to report them also 0
then create a table that holds 1 year of dates and
left or right join the output of first query with it.
exact query is not being provided , its just an idea.
regds
mallah.
On Friday 04 Apr 2003 4:40 pm, Ali Adams wrote:
Dear All,
I am new to Relational Databases and SQL and my background in ODBs is
clouding my way to solving what seems to be a simple problem. I am sure
many of you have met it many times.
OK, I have a table as follows:
ID Machine Date Withdrawals
1 1 01/01/20031101
2 2 01/01/20032101
3 3 01/01/20033101
4 1 02/01/20031102
5 2 02/01/20032102
6 1 03/01/20031103
7 3 03/01/20033103
8 2 04/01/20032104
9 4 04/01/20034104
And i would like to create a monthly withdrawals report as follows:
Machine Day1 Day2 Day3 Day4
.. Day31 1 11
12 13 0 0 2
21 22 024
0 3 31 033 0
0 4 00 0
0 0
Can you please help?
Many thanks in advance.
Ali
help yourself by helping others
www.geocities.com/aliadams
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] substr_count
On Thursday 10 Jul 2003 10:08 am, Tom Rochester wrote: Hey all, I would like to achive something along the lines of: SELECT field FROM table WHERE field ILIKE '$searchterm' ORDER BY substr_count(field, '$searchterm'); Hi In case you are attempting to search text in a feild and sort it by relevence then contrib/tsearch V2 is for you. http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ regds mallah. Of course the substr_count function does not exist. Is there anyway to do this? I had a thought char_count(replace(field, !$searchterm, '')) might do the job but replace doesn't allow for 'replace everything NOT string'; Any Ideas? Thanks in advance, -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] Off topic : world database
Hi, can any one help me converting longitudes and latitudes to something that packages like postgis can readily accept. calle.com provides longitude and latitude info of almost all the places in world. fetching that data and inserting in database is not a problem. I am wanting to make an application similar to you which shud be able to get all the places within certaint radius of the place in question. Any hint on how i shud be proceeding? Regds mallah. On Monday 31 Mar 2003 6:02 am, Rudi Starcevic wrote: Hi all, A quick email to let you know where I'm at with the 'world database'. Thanks to all the replies and tips. I've recieved a couple of off list tips and questions so in order to reach every one I'll reply via this list. So far I haven't got all the info together. Countries like the US and Australia are easy - there's plenty of freely available data. Getting the entire globe is another question all together. I stll have some leads to follow up however those remaining leads will surely required some dollars. So far the the best list I have come across is at http://www.calle.com/world. This site has Countries and Cities - what's needed is Countries, States and cities. I know other site's out there are using the info I'm after. One site for example is date.com At this site, a global dating site, no matter which county your from you will drill down into your state and then city. Once your logged in you can search for matches and limit by distance. One of the many uses for a world database. I hope to eventually report back with a url and web services for us all so we can use this info in the greatest database ever - PG. Cheers Kind regards Rudi. Rudi Starcevic wrote: Hi, I have a slightly off SQL topic question which I don't think is too inappropriate, if it is please let me know and I'll not push my luck again of my *favorite email list*. I'd like to build a Postgresql database of Countries,States and Cities of the world. I would like to use 'earthdistance' module so distance queries are possible. I plan to expose this data as an SOAP webservice via xmethods.com for others to access as well. I've been searching for a source for this data without joy so far and would like to ask if someone on this list could point me to or help with a source for this info. Ideally I'd like Country,States,and Cities with Longitude and Latitude coordinates. Thank you kindly, Again if this is too off topic let me know and I'll keep all my questions strictly to SQL. Regards Rudi. ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] FUNCTIONS PROBLEM
CREATE TYPE can be used as well i think in that case as (val1 int, val2 int, val3 real, val4 char); need not be done while selecting. regds mallah On Monday 24 Mar 2003 4:48 am, David Witham wrote: Hi Mario, I have used a record type to do this: create myfunc() returns record as ' declare return_val record; col1int; col2int; col3real; col4char; col1 := 5; col2 := 10; col3 := 2.7; col4 := ''z''; select col1,col2,col3,col4 into return_val; return return_val; end; ' language 'plpgsql'; When you call the function you need to specify the expected output: select * from myfunc() as (val1 int, val2 int, val3 real, val4 char); See the SELECT reference page in the documentation. There are other ways (which may be better) to do this that don't require the output types to be specified with the query but this is the one I got going first so I stuck with it. Hope this helps. Regards, David Witham Telephony Platforms Architect Unidial -Original Message- From: Mario Alberto Soto Cordones [mailto:[EMAIL PROTECTED] Sent: Friday, 21 March 2003 09:26 To: [EMAIL PROTECTED] Subject: [SQL] FUNCTIONS PROBLEM Importance: High Hi. i have a function and i need to return 4 fields but not work, any idea , please thank mario ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] explain
Dear Marian, in postgresql 7.3 explain commands retruns as set of rows. The result of EXPLAIN can just be fecthed as result of any other SELECT query and manipulated further. i hope it helps. sorry if i did not get ur problem rite. regds mallah. On Friday 21 Mar 2003 11:51 am, Popeanga Marian wrote: Roberto Mello wrote: On Fri, Mar 21, 2003 at 07:46:01AM +0200, Popeanga Marian wrote: Hello all, Can someone point me how can i make explain plan for a query? explain select blah from foo where bar = baz; \h explain Look at the PostgreSQL documentation (SQL Reference). -Roberto I have look at this command. I want to know how can i make this from other tool than pgsql. In oracle when i issue an explain plan the explain insert values into a table ( plan_table ). Then i could select the values from plan_table. Is there similar in postgres ? -- Popeanga Marian DBA Oracle CNLO Romania -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Novice needs help
shud use NOT EXISTS instead of NOT IN as others have suggested . becoz NOT IN is not very efficient at the moment except the case on there are small number of items in IN( ... ) regds mallah. On Wednesday 12 Mar 2003 2:16 am, Terry Lee Tucker wrote: I answered my own question. Yes, it can be done like this: SELECT code, name, city, country, province FROM cust WHERE (country, province) NOT IN (SELECT country, code FROM province); The query returned two cust records that had bogus province codes in them. Very cool :o) Terry Lee Tucker wrote: I have loaded over 29,000 customer records into a database. I'm trying to apply a referential foreign key that validates the country code and the province code in another table. It won't work because somewhere in that 29,000 records there is a province code or country code that doesn't match. Is there a way to use a select statement to find out which customer records has the invalid data? Here is the partial table layout: custprovince --- country === country province=== code Thanks in advance... -- Sparta, NC 28675 USA 336.372.6812 http://www.esc1.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] sql question regarding count(*)
When does count(*) returns o rows ? and when does it return 1 row value being 0. tradein_clients=# SELECT count(*) from public.eyp_listing where sno 0 and amount 0 group by sno,branch,edition having count(distinct userid) 1 ; count --- (0 rows) tradein_clients=# tradein_clients=# tradein_clients=# SELECT count(*) from public.users where userid=-1; count --- 0 (1 row) tradein_clients=# -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Help with query involving aggregation and joining.
the latest submission for each course in a table as shown below, order by name of the courses. Query Results: == id | courseId | name| submission --- 4 | 102 | Chemisty | 2002-02-22 3 | 104 | Maths| 2002-04-30 1 | 101 | Physics | 2002-01-20 I think you want to do something like: select distinct on (course.courseid) history.id, course.courseid, course.name, history.submission from course natural join history order by course.courseid, history.submission desc; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(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 -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] function defination help ..
Thank you i will look into its source code. and try to find some solution for myself. regds mallah. On Saturday 22 February 2003 07:40 am, Peter Eisentraut wrote: Rajesh Kumar Mallah writes: is it possible to get the function creation defination as produced by pg_dump by some SQL queries on system catalogs? pg_func stores procsrc but i am trying to get RETURNS and the arg part also. You will need to reconstruct what pg_dump does. -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] Adding missing FROM-clause entry for table .... problem.
Hmmm i forgot to follow up. Thanks for pointing out the relevent Docs. Regds Mallah. On Tuesday 18 February 2003 04:04 pm, Christoph Haller wrote: We find that if we alias a tablename and refer to that tablename in where cluase instead of reffering to the alias it produces wrond results. EG: select to_char(a.generated, 'DD/Mon/' ) ,userid,email,descr from membership_invoice a join payment_classes using( payment_class) join users using(userid) join membership_status using(userid) where membership_invoice.status='a' and granted is fa lse and membership_invoice.last_reminder is null and current_date - date(a.generated) 4 limit 10 ; NOTICE: Adding missing FROM-clause entry for table membership_invoice Where as merely rewriting the quer to use defined aliases gives the correct results. select to_char(a.generated, 'DD/Mon/' ) ,userid,email,descr from membership_invoice a join payment_classes using(payment_class) join users using(userid) join membership_status using(userid) where a.status='a' and granted i s false and a.last_reminder is null and current_date - date(a.generated) 4 ; Can Anyone please explain if its a BUG or problem in my understanding I think it's a problem in understanding. The documentation (7.2.1) states (as the NOTICE: does) 2.2.1.3. Table and Column Aliases A temporary name can be given to tables and complex table references to be used for references to the derived table in further processing. This is called a table alias. FROM table_reference AS alias Here, alias can be any regular identifier. The alias becomes the new name of the table reference for the current query -- it is no longer possible to refer to the table by the original name. Thus SELECT * FROM my_table AS m WHERE my_table.a 5; is not valid SQL syntax. What will actually happen (this is a PostgreSQL extension to the standard) is that an implicit table reference is added to the FROM clause, so the query is processed as if it were written as SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a 5; Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] good style?
(3, 'write', 'write permission'); INSERT INTO permission VALUES (4, 'execute', 'execute permission'); INSERT INTO permission VALUES (5, 'modify', 'modify permission'); INSERT INTO permission VALUES (6, 'list', 'list permission'); CREATE TABLE acl_entry ( acl_entry_id int2 NOT NULL, acl_id int2 NOT NULL, permission_id int2 NOT NULL, CONSTRAINT acl_entry_pkey PRIMARY KEY (acl_entry_id), CONSTRAINT fk_acl FOREIGN KEY (acl_id) REFERENCES acl (acl_id), CONSTRAINT fk_permission FOREIGN KEY (permission_id) REFERENCES permission (permission_id) ) WITH OIDS; -- acl_entry for mmcms.access acl with access permission -- acl 'mmcms.access' has 'access' permission INSERT INTO acl_entry VALUES (1, 1, 1); -- acl 'mmcms.system' has 'read' permission INSERT INTO acl_entry VALUES (2, 2, 2); -- acl 'mmcms.system' has 'write' permission INSERT INTO acl_entry VALUES (3, 2, 3); -- acl 'mmcms.admin' has 'read' permission INSERT INTO acl_entry VALUES (4, 3, 2); -- acl 'mmcms.admin' has 'write' permission INSERT INTO acl_entry VALUES (5, 3, 3); CREATE TABLE users ( users_id numeric(20, 0) NOT NULL, mandant_id numeric(20, 0) NOT NULL, language_id int2 NOT NULL, login_name varchar(50) NOT NULL, password varchar(15) NOT NULL, first_name varchar(20) NOT NULL, last_name varchar(20) NOT NULL, creation_date timestamp NOT NULL, last_login_date timestamp, status int2 NOT NULL, CONSTRAINT users_pkey PRIMARY KEY (users_id), CONSTRAINT fk_mandant FOREIGN KEY (mandant_id) REFERENCES mandant (mandant_id), CONSTRAINT fk_language FOREIGN KEY (language_id) REFERENCES language (language_id) ) WITH OIDS; CREATE UNIQUE INDEX users_login_name_idx ON users (login_name); INSERT INTO users VALUES (1, 1, 1, '[EMAIL PROTECTED]', 'test', 'Rafal', 'Kedziorski', now(), NULL, 0); CREATE TABLE groups ( groups_id numeric(20, 0) NOT NULL, mandant_id numeric(20, 0) NOT NULL, name varchar(20) NOT NULL, description varchar(200) NOT NULL, creation_date timestamp NOT NULL, CONSTRAINT groups_pkey PRIMARY KEY (groups_id), CONSTRAINT fk_mandant FOREIGN KEY (mandant_id) REFERENCES mandant (mandant_id) ) WITH OIDS; CREATE UNIQUE INDEX groups_name_idx ON groups (mandant_id, name); -- every mandant should have own access group INSERT INTO groups VALUES (1, 1, 'access', 'access group', now()); INSERT INTO groups VALUES (2, 1, 'system', 'system group', now()); INSERT INTO groups VALUES (3, 1, 'admin', 'admin group', now()); CREATE TABLE users_2_groups ( users_2_groups_id numeric(20, 0) NOT NULL, users_id numeric(20, 0) NOT NULL, groups_id numeric(20, 0) NOT NULL, valid_from timestamp NOT NULL, expired timestamp, CONSTRAINT users_2_groups_pkey PRIMARY KEY (users_2_groups_id), CONSTRAINT fk_groups FOREIGN KEY (groups_id) REFERENCES groups (groups_id), CONSTRAINT fk_users FOREIGN KEY (users_id) REFERENCES users (users_id) ) WITH OIDS; CREATE UNIQUE INDEX users_2_groups_usersgroups__idx ON users_2_groups (users_id, groups_id); INSERT INTO users_2_groups VALUES (1, 1, 1, now(), NULL); INSERT INTO users_2_groups VALUES (2, 1, 2, now(), NULL); INSERT INTO users_2_groups VALUES (3, 1, 3, now(), NULL); CREATE TABLE groups_2_acl_entry ( groups_2_acl_entry_id numeric(20, 0) NOT NULL, groups_id numeric(20, 0) NOT NULL, acl_entry_id int2 NOT NULL, CONSTRAINT groups_2_acl_entry_pkey PRIMARY KEY (groups_2_acl_entry_id), CONSTRAINT fk_groups FOREIGN KEY (groups_id) REFERENCES groups (groups_id), CONSTRAINT fk_acl_entry FOREIGN KEY (acl_entry_id) REFERENCES acl_entry (acl_entry_id) ) WITH OIDS; INSERT INTO groups_2_acl_entry VALUES (1, 1, 1); INSERT INTO groups_2_acl_entry VALUES (2, 2, 2); INSERT INTO groups_2_acl_entry VALUES (3, 2, 3); INSERT INTO groups_2_acl_entry VALUES (4, 3, 4); INSERT INTO groups_2_acl_entry VALUES (5, 3, 5); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] function defination help ..
Hi, is it possible to get the function creation defination as produced by pg_dump by some SQL queries on system catalogs? pg_func stores procsrc but i am trying to get RETURNS and the arg part also. -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] cannot EXPLAIN query...
) |
fax| character varying(100) |
email | character varying(100) |
contact| character varying(100) |
website| character varying(100) |
keywords | text |
show_ad| character varying(25)| default 'f'
status | character varying(200) | default 'a'
amount | integer |
group_id | integer | default
nextval('eyp_listing_group_id_seq'::text)
list_id| integer | not null default
nextval('eyp_listing_list_id_seq'::text)
catalog_id | integer | default 0
generated | date | default date('now'::text)
edition| smallint |
wrong_last_updated | date |
last_updated | timestamp with time zone | default now()
user_keywords | text |
counter| smallint | default 0
sent_on| timestamp with time zone | default now()
max_emails | smallint |
emails_sent| smallint | default 0
total_emails | smallint | default 0
notification | boolean |
branding_id| integer |
keywordidx | txtidx |
company_id | integer |
website_working| boolean | default 'f'
hide_email | boolean | default 'f'
co_name_index | txtidx |
bankers| character varying(200) |
estd | integer |
staff | integer |
annual_turn_value | numeric |
mobile | character varying(50)|
reminder_cnt | smallint | default 0
expires_on | date |
Indexes: eyp_listing_pkey primary key btree (list_id),
eyp_listing_br_cid_cat_id unique btree (company_id, category_id) WHERE (size
= 'BRANDING'::character varying),
a_gist_key gist (keywordidx),
eyp_listing_amt btree (amount),
eyp_listing_branch btree (branch) WHERE (amount 0),
eyp_listing_category_id btree (category_id),
eyp_listing_co_name btree (co_name),
eyp_listing_co_name_index gist (co_name_index),
eyp_listing_company_id btree (company_id),
eyp_listing_email btree (email),
eyp_listing_group_id btree (group_id),
eyp_listing_size btree (size),
eyp_listing_sno_branch btree (branch, sno),
eyp_listing_userid btree (userid)
Check constraints: branding_check CASE WHEN (size = 'BRANDING'::character varying)
THEN ((company_id IS NOT NULL) AND (company_id 0)) ELSE (company_id IS NULL) END
Foreign Key constraints: referrer_branches_master FOREIGN KEY (branch) REFERENCES
tradeindia_branches(branch) ON UPDATE NO ACTION ON DELETE NO ACTION
Triggers: RI_ConstraintTrigger_29292778,
RI_ConstraintTrigger_29292779,
co_name_index_update,
last_updated,
set_category,
set_max_emails
tradein_clients=#
On Monday 03 February 2003 08:16 pm, Tom Lane wrote:
Rajesh Kumar Mallah. [EMAIL PROTECTED] writes:
tradein_clients=# explain SELECT count(*) from shippers1 where
city='DELHI'; ERROR: get_names_for_var: bogus varno 5
What version is this? ISTR having fixed some bugs that might cause that.
i can paste the nasty view definations if nothing is obvious till
now.
If it's a current release, we need to see *all* the schema definitions
referenced by the query --- views and tables.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
--
Regds Mallah
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)26152172 (221) (L) 9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] cannot EXPLAIN query...
Thank you . But i have a problem , I think if i do that i will hve to immediately upgrade all the 7.3.0 clients in other machines to 7.3.1 rite? regds Mallah. On Monday 03 February 2003 09:10 pm, Tom Lane wrote: Rajesh Kumar Mallah. [EMAIL PROTECTED] writes: It is PostgreSQL 7.3.0 on Linux. Try 7.3.1 then. I think this is this problem: 2002-12-06 14:28 tgl * src/backend/commands/explain.c (REL7_3_STABLE): Explain's code for showing quals of SubqueryScan nodes has been broken all along; not noticed till now. It's a scan not an upper qual ... 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])
Re: [SQL] cannot EXPLAIN query...
Thanks , if that is so i am upgrading it right away and posting you the results. Its my live DB server :-) Regds mallah. On Monday 03 February 2003 09:15 pm, Tom Lane wrote: Rajesh Kumar Mallah. [EMAIL PROTECTED] writes: I think if i do that i will hve to immediately upgrade all the 7.3.0 clients in other machines to 7.3.1 rite? No. regards, tom lane -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] cannot EXPLAIN query...
On Monday 03 February 2003 09:20 pm, Christoph Haller wrote: Sorry Postgresql has really made my VIEWS ugly. It wasnt' so when i fed them. I wish pgsql stores the create view defination some day , just like it does for indexes (pg_get_indexdef) Did you ever try SELECT * FROM pg_views ; i thing when you do a \d view_name it uses that only. in any case i have verified that the content in them are equally messed up. regds mallah. It definitely has all view definitions. Regards, Christoph -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] cannot EXPLAIN query...
Hmmm... upgrade to 7.3.1 was not that smooth.. after upgrade i could not run a single query.. tradein_clients= SELECT * from hogs; ERROR: current transaction is aborted, queries ignored until end of transaction block tradein_clients= any other query seems to be giving the same ERROR. check the message below on psql start (7.3.1) with a 7.3.1 server. PS: i applied the heir patch though ... ;-) will try again without that. [postgres@ns3 postgres]$ psql -Upostgres -h66.234.10.12 tradein_clients ERROR: nodeRead: did not find '}' at end of plan node Welcome to psql 7.3.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit tradein_clients= regds mallah. On Monday 03 February 2003 09:15 pm, Tom Lane wrote: Rajesh Kumar Mallah. [EMAIL PROTECTED] writes: I think if i do that i will hve to immediately upgrade all the 7.3.0 clients in other machines to 7.3.1 rite? No. regards, tom lane -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Which version is this?
your question doesnt' seem to be very clear. But the following appeared in release note of version 7.2 hope it helps regds mallah. ` A.5. Release 7.2 Release date: 2002-02-04 A.5.1. Overview This release improves PostgreSQL for use in high-volume applications. Major changes in this release: VACUUM Vacuuming no longer locks tables, thus allowing normal user access during the vacuum. A new VACUUM FULL command does old-style vacuum by locking the table and shrinking the on-disk copy of the table. ~~ On Friday 31 January 2003 10:13 pm, Wei Weng wrote: Since which version PostgreSQL is able to do Vacuum Analyze even in the middle of a transaction, namely, insert, delete, update? Thanks Wei -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] converting microsoft sql server 2000 sql-code for postgresql 7.2.1
Only 1 small change makes it acceptable to pgsql. change datetime to timestamp . regds mallah. On Tuesday 28 January 2003 03:38 pm, william windels wrote: Hello all, I a m a new member of the list and at the moment , I am studiing informatica: sql. At the workplace, we use microsoft sql server 2000. At home, I use postgresql 7.2.1 and now I would import the data of the database at the workplace into the postgresql environment at home. I have paste a little part of the sql-code to create a table in a database called tennisclub. To execute the code bellow with pgsql, I do the following steps: pgsql tennisclub \e file_with_sql_code.sql The contens of the file file_with_sql_code.sql is as follows: CREATE TABLE SPELERS (SPELERSNR SMALLINT NOT NULL, NAAM CHAR(15) NOT NULL, VOORLETTERS CHAR(3) NOT NULL, VOORVOEGSELS CHAR(7) , GEB_DATUM datetime , GESLACHT CHAR(1) NOT NULL, JAARTOE SMALLINT NOT NULL, STRAAT CHAR(15) NOT NULL, HUISNR CHAR(4) , POSTCODE CHAR(6) , PLAATS CHAR(10) NOT NULL, TELEFOON CHAR(10) , BONDSNR CHAR(4) , PRIMARY KEY (SPELERSNR) ); INSERT INTO SPELERS VALUES ( 6, 'Permentier', 'R', NULL, '1964-06-25', 'M', 1977, 'Hazensteinln', '80', '1234KK', 'Den Haag', '070-476537', '8467' ); INSERT INTO SPELERS VALUES ( 44, 'Bakker', 'E', 'de', '1963-01-09', 'M', 1980, 'Lawaaistraat', '23', 'LJ', 'Rijswijk', '070-368753', '1124' ); INSERT INTO SPELERS VALUES ( 83, 'Hofland', 'PK', NULL, '1956-11-11', 'M', 1982, 'Mariakade', '16a', '1812UP', 'Den Haag', '070-353548', '1608' ); INSERT INTO SPELERS VALUES ( 2, 'Elfring', 'R', NULL, '1948-09-01', 'M', 1975, 'Steden', '43', '3575NH', 'Den Haag', '070-237893', '2411' ); INSERT INTO SPELERS VALUES ( 27, 'Cools', 'DD', NULL, '1964-12-28', 'V', 1983, 'Liespad', '804', '8457DK', 'Zoetermeer', '079-234857', '2513' ); INSERT INTO SPELERS VALUES ( 104, 'Moerman', 'D', NULL, '1970-05-10', 'V', 1984, 'Stoutlaan', '65', '9437AO', 'Zoetermeer', '079-987571', '7060' ); INSERT INTO SPELERS VALUES ( 7, 'Wijers', 'GWS', NULL, '1963-05-11', 'M', 1981, 'Erasmusweg', '39', '9758VB', 'Den Haag', '070-347689', NULL ); INSERT INTO SPELERS VALUES ( 57, 'Bohemen', 'M', 'van', '1971-08-17', 'M', 1985, 'Erasmusweg', '16', '4377CB', 'Den Haag', '070-473458', '6409' ); INSERT INTO SPELERS VALUES ( 39, 'Bischoff', 'D', NULL, '1956-10-29', 'M', 1980, 'Ericaplein', '78', '9629CD', 'Den Haag', '070-393435', NULL ); INSERT INTO SPELERS VALUES ( 112, 'Baalen', 'IP', 'van', '1963-10-01', 'V', 1984, 'Vosseweg', '8', '6392LK', 'Rotterdam', '010-548745', '1319' ); INSERT INTO SPELERS VALUES ( 8, 'Niewenburg', 'B', NULL, '1962-07-08', 'V', 1980, 'Spoorlaan', '4', '6584WO', 'Rijswijk', '070-458458', '2983' ); INSERT INTO SPELERS VALUES ( 100, 'Permentier', 'P', NULL, '1963-02-28', 'M', 1979, 'Hazensteinln', '80', '6494SG', 'Den Haag', '070-494593', '6524' ); INSERT INTO SPELERS VALUES ( 28, 'Cools', 'C', NULL, '1963-06-22', 'V', 1983, 'Oudegracht', '10', '1294QK', 'Leiden', '010-659599', NULL ); INSERT INTO SPELERS VALUES ( 95, 'Meuleman', 'P', NULL , '1963-05-14', 'M', 1972, 'Hoofdweg', '33a', '5746OP', 'Voorburg', '070-867564', NULL ); This code doesn't work. Can someone tell me how I can adjust the syntax of the code and in global: how can I convert sql-code , for microsoft sql server 2000, to sql-code for postgresql? Thanks in advance best regards William Windels ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] Implementing automatic updating of primary keys...
yes you got my problem rightly. If i use on update cascade approach still there is problem. If i attempt to update the key in master table it wont be allowed becoz of temporary violation of PRIMARY KEY CONSTRAINT. becoz 1 is also existing in the master table. update profile_master set id=1 where id=2 will not be accepted. regds mallah. On Wednesday 01 January 2003 06:11 pm, Tomasz Myrta wrote: Rajesh Kumar Mallah. wrote: Hi we are working on re-structuring our database schemas and intend to implement the functionality below at database level. consider a master table with following data. Table: profile_master id | username | password ---|--|-- 1 | u1 | p1 2 | u2 | p2 id-- primary key not null. can some thing be done in the database level it self so that we do not have to keep modifying the mantainence programs as the number of tables referencing master table grows? regds mallah. If I understood well you want to change id in all tables from some value into another one and no matter, how many these tables exist? First - if your tables are created with on update cascade, you can just change value on master table. If you didn't create tables with this option and referencing key has the same name in all tables, it isn't still too difficult. Everything you need is a function which finds all tables with field id and for each table performs: update table set id=newvalue where id=oldvalue. In plpgsql it will look something like: create or replace function... declare oldvalue alias for $1; newvalue alias for $2; tablename varchar; begin for tablename in SELECT relname from pg_attribute join pg_class on (attrelid=oid) where attname=''id'' and relkind='r'; loop perform ''update '' || tablename '' set id='' || newvalue || '' where id='' || oldvalue; end loop; end; Many interesting find about database special tables you will find in Chapter 3. System Catalogs inside Postgresql documentation. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] sort by relevance
Yet another customer for relevence ranking ;-) malz. On Wednesday 15 January 2003 11:49 pm, Oleg Bartunov wrote: On Wed, 15 Jan 2003 [EMAIL PROTECTED] wrote: HI, PPL! How am I able to sort query results by relevance? I use contrib/tsearch to search using fill text index! Use OpenFTS (openfts.sourceforge.net) for relevance ranking. we might add relevance feature to tsearch though. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] Search and Replace
REPLACE is the right function for you avaliable as an addon. install it in postgresql installation using the source at: http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=23 if you face problem please lemme know once this function is installed you could update like: -- UPDATE publications SET url = replace( 'www.srs.fs.fed.us' , 'www.srs.fs.usda.gov' , url ) WHERE url ilike '%www.srs.fs.fed.us%'; -- regds mallah. On Wednesday 08 January 2003 07:32 pm, Randy D. McCracken wrote: I apologize for having to resort to sending what is most likely a simple tech support question regarding PostgreSQL to this list but I have not been able to find the answer in the documentation. I am responsible for managing a database containing over 6,000 records of US Forest Service Research publications (http://www.srs.fs.usda.gov/pubs/search.jsp) and I need to do a simple search and replace in one of the columns. In these records we have a field for URLs of the location the research publications and I need to change the base URL from www.srs.fs.fed.us to www.srs.fs.usda.gov. It seems like this search and replace would be a simple thing to do with an UPDATE command but I am having great difficulty making this work. The table definition I am trying to update is: url. At first I thought about using a simple UPDATE command like this: UPDATE publications SET url = 'www.srs.fs.usda.gov' WHERE url = 'www.srs.fs.fed.us'; Of course that would work fine but www.srs.fs.fed.us is only part of a complete URL such as: http://www.srs.fs.fed.us/pubs/rp/rp_se263.pdf. My problem (among other things!) is that I don't know how to pass along wildcards so that I do not change the other parts of the complete URL. I have tried substituting like for = and trying to use the wildcard of % but to no avail. I am really just guessing here. Any help would be greatly appreciated! Best, --rdm === Randy D. McCracken (0 Web Guy //\ Communications GroupV_/_ USDA/FS - Southern Research Station E-Mail: [EMAIL PROTECTED] Voice:(828) 259-0518 Fax: (828) 257-4840 Web: http://www.srs.fs.fed.us/ === ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Search and Replace
any anyone explain whats wrong with the replace based solution to this problem which i posted earlier? did i misunderstood anything? regds mallah. On Thursday 09 January 2003 01:48 am, Randy D. McCracken wrote: Just to close off another thread and to give a tad more information... I was not clear enough in my initial question to the list because not all of the URLs I am trying to change begin with http://www.srs.fs.fed.us/ so what I was really looking for was the syntax for replacing www.srs.fs.fed.us with www.srs.fs.usda.gov and not touching any records do not contain www.srs.fs.fed.us Ross Reedstrom was kind enough to give me some additional help that worked perfectly and after doing a few tests I am happy to share his SQL statement with the list. update pubs set url=substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||s ubstr(url,strpos(url,'www.srs.fs.fed.us')+17) where url ~ 'www.srs.fs.fed.us' Thanks Ross! --rdm On Wed, 8 Jan 2003, Ross J. Reedstrom wrote: snip description of needing a simple string replace As you've discovered, standard SQL text processing functions are a bit primitive - usually you break out to the application language for that sort of thing. However, if you know for sure that there's only one instance of the replace string, and it's a fixed length string, you can get away with something like this: test=# select * from pubs; id | url + 1 | http://www.srs.fs.fed.us/pub/1 2 | http://www.srs.fs.fed.us/pub/2 3 | http://www.srs.fs.fed.us/pub/3 (3 rows) test=# update pubs set url= substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||sub str(url,strpos(url,'www.srs.fs.fed.us')+17); UPDATE 3 test=# select * from pubs; id | url +-- 1 | http://www.srs.fs.usda.gov/pub/1 2 | http://www.srs.fs.usda.gov/pub/2 3 | http://www.srs.fs.usda.gov/pub/3 (3 rows) You can figure out how it works by playing with SELECTing different substr() ans strpos() directly, like this excerpt from my query history: select strpos(url,'www.srs.fs.usda.gov') from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,1,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,-1,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')+18) from pubs; select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs; Hope this helps, Ross -- Ross Reedstrom, Ph.D. [EMAIL PROTECTED] Research Scientist phone: 713-348-6166 The Connexions Project http://cnx./rice.edu fax: 713-348-6182 Rice University MS-39 Houston, TX 77005 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] COPY command problems
http://www.memtest86.com/ may be useful. regds mallah. On Tuesday 24 December 2002 09:25 pm, Nikola Ivacic wrote: It must be internal error: two reasons: 1.) the original file is OK (I checked with grep + there is no network envolved) 2.) Error has strange patern: it substitutes 0x31 with 0x21 (1 with !) also 0x34 with 0x24 (4 with $) and 0x39 with 0x29 (9 with ) ) so I guess you are right. can you suggest some tools for FreeBSD to test RAM, because I think the hard disk is ok. p.s. right now I am testing it with splited file thanks Nikola - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Nikola Ivacic [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, December 24, 2002 4:24 PM Subject: Re: [SQL] COPY command problems Nikola Ivacic [EMAIL PROTECTED] writes: Has somebody experienced difficulties using COPY command on large files. I have a large (250MB) file and each time I insert records I've got one or = more ( 30 of cca 1079000) corrupted records. The number of corrupted records is not constant (i.e. 1, 30, 7, 23= etc..) I'd bet on flaky hardware --- have you run memory and disk tests? If the COPY data is passing across a network, then network problems are also worthy of suspicion. 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]) -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] function replace doesnt exist
Are you looking for this ?
available on
http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=23
regds
mallah.
View One Recipe
Home - Postgres - CookBook Home - View One Recipe Submitted on: 03-16-2001
Description:
mimic oracle's replace function. versions in pltcl and plpgsql.
Code:
-- by Jonathan Ellis ([EMAIL PROTECTED])
-- licensed under the GPL
-- emailing me improvements is appreciated but not required
-- args: string substring replacement_substring
create function replace (varchar, varchar, varchar) returns varchar as '
-- escape out characters that regsub would treat as special
regsub -all {} $3 {\\\} 3
regsub -all {\\[0-9]} $3 {\\\0} 3
eval regsub -all \{$2\} \{$1\} \{$3\} rval
return $rval
' language 'pltcl';
-- plpgsql version so we don't have to jump through hoops to call it from other
functions
create function replace (varchar, varchar, varchar) returns varchar as '
declare
string alias for $1;
sub alias for $2;
replacement alias for $3;
-- xxx[MATCH]
-- | end_before
-- | start_after
match integer;
end_before integer;
start_after integer;
string_replaced varchar;
string_remainder varchar;
begin
string_remainder := string;
string_replaced := ;
match := position(sub in string_remainder);
while match 0 loop
end_before := match - 1;
start_after := match + length(sub);
string_replaced := string_replaced || substr(string_remainder, 1, end_b
efore) || replacement;
string_remainder := substr(string_remainder, start_after);
match := position(sub in string_remainder);
end loop;
string_replaced := string_replaced || string_remainder;
return string_replaced;
end;
' LANGUAGE 'plpgsql';
On Thursday 12 December 2002 10:04 pm, Andy Morrow wrote:
Hi
im trying to execute an update command on a postgresql DB table using
pgAdmin II
im using the following statement
UPDATE commandlist SET command = REPLACE (command,'A','B')
commandlist is the table name
command is the column
and i want to change the value A to B
but it's giving me the following error message
an error has occured in pgAdmin II:frmSQLInput.cmdExecute_Click:
Number: -2147467259
Description: Error while executing the query;
ERROR: Function'replace(varchar, unknown, unknown)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
---(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
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Stored Procedure Problem
In 7.3 you can , in follwoing steps, 1. do a CREATE TYPE (i would recommend to use a sperate schema for storing user defined types) 2. in plpgsql declare the RECORD of that type . 3. populate the record varible according to your business logic and return the RECORD using RETURN statements. hope it will help , if not please revert back. regds mallah. On Thursday 12 December 2002 08:21 pm, Tomasz Myrta wrote: Atul wrote: CREATE FUNCTION b_function() RETURNS varchar AS ' DECLARE an_integer int4; an_namevarchar; BEGIN select into an_integer emp_id,an_name emp_name from employee; return an_integer,an_name; END; ' First: select into an_integer,an_name emp_id,emp_name... Second: you can't return 2 variables from plpgsql function. Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] why the difference?
Hi folk, i am finding something mysterious in SQL can anyone explain? consider the SQL: tradein_clients=# select distinct on (amount,co_name,city) category_id,amount,co_name,city from eyp_listing where keywordidx ## 'vegetable' and category_id=781 ; category_id | amount | co_name |city -++---+ 781 | 0 | ANURADHA EXPORTS | CHENNAI 781 | 0 | R.K.INTERNATIONAL | CHENNAI 781 | 0 | SAI IMPEX | MUMBAI 781 | 0 | TRIMA ENTERPRISES | CHENNAI 781 | 0 | UNIQUE DEHYDRATES LIMITED | AHMEDABAD 781 | 5000 | RSV EXPORT| COIMBATORE (6 rows) lets remove the contraint category_id=781 and store the output in a table t_a. tradein_clients=# CREATE TABLE t_a AS select distinct on (amount,co_name,city) category_id,amount,co_name,city from eyp_listing where keywordidx ## 'vegetable' ; then when i select from t_a with category_id=781 i have less secords tradein_clients=# SELECT * from t_a where category_id=781; category_id | amount | co_name |city -++---+ 781 | 0 | R.K.INTERNATIONAL | CHENNAI 781 | 0 | SAI IMPEX | MUMBAI 781 | 0 | UNIQUE DEHYDRATES LIMITED | AHMEDABAD 781 | 5000 | RSV EXPORT| COIMBATORE (4 rows) Can anyone please explain the difference? Regds Mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] enforcing with unique indexes..
Hi , can anyone tell me how can i enforce below in a table. I want that no more that one distinct userid exists for a given group_id in the table. ie i want 1 to 1 mapping between group_id and userid so that , there shud not be a single group_id having more that one kind of userid. SELECT group_id from eyp_listing group by group_id having count(distinct userid) 1 ; always returns empty. can it be done with some sort of UNIQUE INDEX? Regds MAllah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] enforcing with unique indexes..
Sorry Bhuvan it wont work, COMPOSITE UNIQUE INDEX will prevent entry of rows like (group_id,user_id) 1 1 1 1 what i want to prevent is this: 1 1 1 2 did you notice the distinct inside the count? regds mallah. On Saturday 05 October 2002 12:36, Bhuvan A wrote: SELECT group_id from eyp_listing group by group_id having count(distinct userid) 1 ; always returns empty. can it be done with some sort of UNIQUE INDEX? Yes it can be done using UNIQUE INDEX. Create a composite unique index on these 2 fields and it will do the rest. For more details, try $ \h CREATE INDEX regards, bhuvaneswaran -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] check source of trigger
Hi, In case you doing all this to replicate tables conside contrib/dbmirror it does it fairly elegantly. regds mallah. On Friday 20 September 2002 13:55, wit wrote: Hello, I have a question about trigger. I have tables with the following structure: create table A ( e_codeA char(5) default '' not null, n_codeA varchar(20) default '' not null, constraint A_pkey primary key ( e_codeA ) ); create table B ( e_codeB char(5) default '' not null, e_codeA char(5) default '' not null constraint e_codeA_ref references A( e_codeA ) on delete cascade on update cascade, n_codeB varchar(20) default '' not null, constraint B_pkey primary key ( e_tranB, e_codeA ) ); I have trigger and procedure on table B to capture any change and insert into table logB: create trigger trigger_b before insert or update or delete on B for each row execute procedure log_change(); When I update e_codeA in table A, the constrain trigger will update e_codeA in B. My trigger, trigger_b, also was trigged and procedure will record change into table logB too. How to write a code in my db procedure to check whether the procedure was called by normal SQL or was called by cascade trigger. Regards, wit ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] Preventing DELETEs
Hi , I have a created a database and a table in it, I want to prevent DELETES on the table in this database by everyone except superuser postgres. even by me (the creator of this database and table) I have tried in many ways (ALTER TABLE REVOKE DELETE .. etc etc) but i always end up with having the permission can any one tell me how the prevention can be accomplished? thanks in advance. regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] Preventing DELETEs
Hi Dima, I currently have only one user in the system its me and superuser postgres. every thing belongs to me currently. and the programs connect as me. if make transfer the database ownership to postgres will all the tables also get transfered to him? it that case all programs will stop working. can i transefer database ownership to postgres and allow myself ALL the PREVILEGES and selectively REVOKE the DELETE permission from myself on the concerned table? If that is possible could you kindly tell me the commands Current state is: tradein_clients= \l List of databases Name | Owner -+-- template0 | postgres template1 | postgres tradein_clients | tradein (this is me) (3 rows) tradein_clients= regds Mallah. On Friday 27 September 2002 00:30, dima wrote: I have a created a database and a table in it, I want to prevent DELETES on the table in this database by everyone except superuser postgres. even by me (the creator of this database and table) make superuser the database owner grant the rights needed to the users -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Preventing DELETEs
Hi Robert, I will be obliged to receive the real code , if its feasible for you. I am not used RULEs before. regds mallah. On Friday 27 September 2002 00:39, Robert Treat wrote: In psuedo-code : create rule on mytable on delete return null Robert Treat On Thu, 2002-09-26 at 15:00, Rajesh Kumar Mallah. wrote: Hi , I have a created a database and a table in it, I want to prevent DELETES on the table in this database by everyone except superuser postgres. even by me (the creator of this database and table) I have tried in many ways (ALTER TABLE REVOKE DELETE .. etc etc) but i always end up with having the permission can any one tell me how the prevention can be accomplished? thanks in advance. regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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 -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Preventing DELETEs
Hi Dmitry, Got it working i made a small change. On Friday 27 September 2002 00:47, you wrote: I think this should work: alter table mytable owner to postgres; grant all on my table to public; instead of revoke delete on my table from public; i did : revoke delete on my table from tradein (which is me) ; tradein_clients= BEGIN WORK; delete from users where userid=34866; BEGIN ERROR: users: Permission denied. tradein_clients= ROLLBACK ; ROLLBACK tradein_clients= UPDATE users set password='mallah' where userid=34866; UPDATE 1 tradein_clients= does public not include me?? regds mallah. I hope, it helps... Dima Rajesh Kumar Mallah. wrote: Hi , I have a created a database and a table in it, I want to prevent DELETES on the table in this database by everyone except superuser postgres. even by me (the creator of this database and table) I have tried in many ways (ALTER TABLE REVOKE DELETE .. etc etc) but i always end up with having the permission can any one tell me how the prevention can be accomplished? thanks in advance. regds mallah. --=20 Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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 -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Index usage on date feild , differences in '' and '=' and between
Hi , I am trying to improve a query to use existing indexes but facing diffculty. Looks like 'between' amd '=' are not using indexes althoug and does. all my application code uses between and i am sure it use to work fine at one point of time. regds mallah. SQL TRANSCRIPT: == tradein_clients= explain select list_id from eyp_rfi a where generated '2002-08-13' and generated '2002-09-19' ; NOTICE: QUERY PLAN: Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..15796.97 rows=4150 width=4) EXPLAIN tradein_clients= explain select list_id from eyp_rfi a where generated = '2002-08-13' and generated '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4) EXPLAIN tradein_clients= explain select list_id from eyp_rfi a where generated = '2002-08-13' and generated = '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4) EXPLAIN tradein_clients= explain select list_id from eyp_rfi a where generated between '2002-08-13' and '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4) EXPLAIN tradein_clients= == -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Index usage on date feild , differences in '' and '=' and between
Thanks very much for the response. set enable_seqscan=off; Definitely helps. and for wide date ranges it usees indexes. But with default value of enable_sequence changing date range seems to have effect. can you explain me a bit more or point me to right documents for understanding the languae of EXPLAIN. EXPLAIN tradein_clients= explain select list_id from eyp_rfi a where generated between '2002-09-11' and '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=12924 width=4) EXPLAIN tradein_clients= explain select list_id from eyp_rfi a where generated between '2002-09-12' and '2002-09-19' ; NOTICE: QUERY PLAN: Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..17369.05 rows=12220 width=4) EXPLAIN tradein_clients= == the distribution of values are as follows: select generated ,count(generated) from eyp_rfi a where generated between '2002-09-10' and '2002-09-19' group by generated; generated | count +--- 2002-09-10 | 442 2002-09-11 | 1060 2002-09-12 | 641 2002-09-13 | 607 2002-09-14 | 1320 2002-09-15 | 521 2002-09-16 | 1474 2002-09-17 | 940 2002-09-18 | 1005 2002-09-19 | 178 (10 rows) Last Question , Shud i do enable_seqscan=off in Application Level? I use Perl-DBI Thanks and Regards Rajesh Mallah. Tsday 19 September 2002 12:07, Stephan Szabo wrote: On Thu, 19 Sep 2002, Rajesh Kumar Mallah. wrote: I am trying to improve a query to use existing indexes but facing diffculty. Looks like 'between' amd '=' are not using indexes althoug and does. all my application code uses between and i am sure it use to work fine at one point of time. regds mallah. SQL TRANSCRIPT: = = tradein_clients= explain select list_id from eyp_rfi a where generated '2002-08-13' and generated '2002-09-19' ; NOTICE: QUERY PLAN: Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..15796.97 rows=4150 width=4) EXPLAIN tradein_clients= explain select list_id from eyp_rfi a where generated = '2002-08-13' and generated '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4) Given how close the two results are (and the fact that they differ by 900 rows), have you tried using set enable_seqscan=off and seeing what explain gives you for the second query? My guess is that it'll have an estimated cost greater than the 17923.81 it's estimating from the sequence scan. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] BITMAP INDEXES...
Hi, I have learnt from certain texts that bitmap indexes are very useful for large DSS (decesion support systems). bitmap indexes are currently not available in postgresql. My question is in what way is a normal btree index in pgsql inferior to bitmap indexes (found in oracle for example). is it just in terms of space requirements for performance too? regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[SQL] Difference between is true and = 't' in boolean feild. bitmap indexes
Any can anyone explain me why in a query of a boolean feild is ture does not indexes where as = 't' does? is is true not a more standard SQL than = 't' . Also is there any working implementation of BITMAP INDEXES in postgresql as found in ORACLE? regds mallah. tradein_clients= explain analyze select c.email,date(a.generated),c.expired from eyp_rfi a join users b on (a.receiver_uid = b.userid) join grace_mytradeindia c on (b.email = c.email) where generated_date = '2002-08-17' and c.expired is true; NOTICE: QUERY PLAN: Hash Join (cost=1948.01..2214.60 rows=14 width=67) (actual time=9.32..40.03 rows=12 loops=1) - Seq Scan on grace_mytradeindia c (cost=0.00..246.17 rows=4051 width=26) (actual time=0.65..27.76 rows=5372 loops=1) - Hash (cost=1947.46..1947.46 rows=218 width=41) (actual time=2.66..2.66 rows=0 loops=1) - Nested Loop (cost=0.00..1947.46 rows=218 width=41) (actual time=0.16..2.57 rows=31 loops=1) - Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..677.81 rows=218 width=12) (actual time=0.09..0.57 rows=50 loops=1) - Index Scan using users_pkey on users b (cost=0.00..5.80 rows=1 width=29) (actual time=0.03..0.03 rows=1 loops=50) Total runtime: 40.26 msec EXPLAIN tradein_clients= explain analyze select c.email,date(a.generated),c.expired from eyp_rfi a join users b on (a.receiver_uid = b.userid) join grace_mytradeindia c on (b.email = c.email) where generated_date = '2002-08-17' and c.expired = 't'; NOTICE: QUERY PLAN: Hash Join (cost=1948.01..2144.70 rows=14 width=67) (actual time=6.26..49.92 rows=12 loops=1) - Index Scan using grace_mytradeindia_exp on grace_mytradeindia c (cost=0.00..176.27 rows=4051 width=26) (actual time=0.03..37.53 rows=5372 loops=1) - Hash (cost=1947.46..1947.46 rows=218 width=41) (actual time=2.15..2.15 rows=0 loops=1) - Nested Loop (cost=0.00..1947.46 rows=218 width=41) (actual time=0.15..2.06 rows=31 loops=1) - Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..677.81 rows=218 width=12) (actual time=0.09..0.48 rows=50 loops=1) - Index Scan using users_pkey on users b (cost=0.00..5.80 rows=1 width=29) (actual time=0.02..0.03 rows=1 loops=50) Total runtime: 50.16 msec EXPLAIN tradein_clients= -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] getting ILIKE or ~* to use indexes....
Hi folks, can anyone tell me or point me to the right thread. I want my query to use indexes for company name searches but its not happening unless is use '=' which does not server the purpose. eg tradein_clients=# explain SELECT co_name FROM unified_data where co_name ilike '%rajesh%' ; NOTICE: QUERY PLAN: Seq Scan on unified_data (cost=0.00..19293.00 rows=1 width=25) EXPLAIN tradein_clients=# explain SELECT co_name FROM unified_data where co_name = 'rajesh' ; NOTICE: QUERY PLAN: Index Scan using unified_data_co_name_key on unified_data (cost=0.00..6.26 rows=1 width=25) EXPLAIN tradein_clients=# Regards mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] possible bug in \df+
Hi there , SELECT prosrc from pg_proc where proname='procedure_name'; and \df+ procedure_name are reporting two different versions for FUNCTION body. eg: \df+ category_path shows: DECLARE v_category_id ALIAS FOR $1; DECLARE tmp_record RECORD; DECLARE tmp_id VARCHAR; DECLARE tmp_code VARCHAR; BEGIN tmp_code := '' ; SELECT INTO tmp_record b.name , a.category_id , a.parent_category_id from category_tree_new a natural inner join categories_new b where category_id=v_category_id and a.link is not true ; IF NOT FOUND THEN RETURN 'Home'; END IF; IF tmp_record.category_id = 0 THEN RETURN tmp_record.name; END IF; tmp_id := category_path_text(tmp_record.parent_category_id) ; IF tmp_record.category_id 0 THEN tmp_code := tmp_id || ' ' || tmp_record.name ; END IF; RETURN tmp_code; END; and select from pg_proc gives DECLARE v_category_id ALIAS FOR $1; DECLARE tmp_record RECORD; DECLARE tmp_id VARCHAR; DECLARE tmp_code VARCHAR; BEGIN tmp_code := '' ; SELECT INTO tmp_record b.name , a.category_id , a.parent_category_id from category_tree_new a natural inner join categories_new b where category_id=v_category_id and a.link is not true ; IF NOT FOUND THEN RETURN '0' || ':' || 'ROOT'; END IF; IF tmp_record.category_id = 0 THEN RETURN tmp_record.category_id || ':' || tmp_record.name ; END IF; tmp_id := category_path(tmp_record.parent_category_id) ; IF tmp_record.category_id 0 THEN tmp_code := tmp_id || '#' || tmp_record.category_id || ':' || tmp_record.name ; END IF; RETURN tmp_code; END; regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Ltree usage..
Hi Oleg, I am trying to use contrib/ltree for one of my applications. the query below works fine for me. Qry1: SELECT path from unified_data where path ~ '*.180.*' and path ~ '*.1.*'; is there any way of compacting it for example Qry2: SELECT path from unified_data where path ~ '*.180.*' or path ~ '*.1.*'; is better written as Qry3: SELECT path from unified_data where path ~ '*.180|1.*' ; also is qry3 better to Qry2 in terms of performance? regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] Ltree usage..
Hi Oleg,
It does not yeild the correct result for me.
I am providing more details this time.
path is ltree [] for me not ltree,
Column | Type |Modifiers
++-
profile_id | integer| not null default
nextval('unified_data_profile_id_seq'::text)
co_name| character varying(255) |
city | character varying(100) |
path | ltree[]|
Indexes: unified_data_path
Unique keys: unified_data_co_name_key,
unified_data_profile_id_key
eg if my sample data set is.
profile_id | path
+--
25477 | {0.180.830,0.180.848}
26130 | {0.180.848}
2928 | {0.64.65,0.64.67,0.180.830,0.180.848,0.180.849}
26129 | {0.180.848}
26126 | {0.180.848}
26127 | {0.180.848}
26128 | {0.180.848}
24963 | {0.180.830,0.180.848}
26125 | {0.180.848}
7239 | {0.246.256,0.246.282,0.180.848,0.246.857,0.76.1255.1161}
(10 rows)
what query shud i use to extract profiles where path contains *.64.* and *.180.*
eg this query
SELECT profile_id,pathfrom unified_data where path ~ '*.180.*' and path ~
'*.64.*' limit 10;
profile_id | path
+-
2928 | {0.64.65,0.64.67,0.180.830,0.180.848,0.180.849}
3238 | {0.64.68,0.180.830,0.395.904}
6255 | {0.180.227,0.64.814}
6153 | {0.180.227,0.505.518,0.64.814}
6268 | {0.180.227,0.64.814}
6267 | {0.180.227,0.64.814}
6120 | {0.180.227,0.64.814}
6121 | {0.180.227,0.64.814}
6084 | {0.180.227,0.64.814}
6066 | {0.180.227,0.64.810}
(10 rows)
gives me the correct result but i am not sure if its the most efficient.
I will be using it for medium sized dataset approx 100,000 that there will be such
search on upto four such indexed columns.
regds
mallah.
On Friday 02 August 2002 22:30, Oleg Bartunov wrote:
On Fri, 2 Aug 2002, Rajesh Kumar Mallah. wrote:
Hi Oleg,
I am trying to use contrib/ltree for one of my applications.
the query below works fine for me.
Qry1: SELECT path from unified_data where path ~ '*.180.*' and path
~ '*.1.*';
is there any way of compacting it for example
Qry2: SELECT path from unified_data where path ~ '*.180.*' or path
~ '*.1.*'; is better written as
Qry3: SELECT path from unified_data where path ~ '*.180|1.*' ;
Qry2 and Qry3 are equvalent and Qry3 is faster but not much.
But Qry1 is not the same as Qry2 !!!
Qry1 could be rewritten as:
SELECT path from unified_data where path @ '180 1';
also is qry3 better to Qry2 in terms of performance?
regds
mallah.
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Is login_cnt is reserved attribute name?
Hi i did nothing but changed attribute name from 'login_cnt' to 'cnt_login' and my update stmt started working? I am using DBD::Pg + postgresql 7.2.1 i will try to produce a test case. regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Cascading deletions does not seem to work inside PL/PGSQL functions.
Thanks for your reply. Stephan. On Thursday 18 July 2002 12:01, you wrote: On Thu, 18 Jul 2002, Rajesh Kumar Mallah. wrote: what if i do not want to fire sperate delete SQLs for the slave tables ? Hi , what is mean is that I a have many tables(x,y,z...) which reference the master table (M). I want that the records from the tables (x,y,z ect) automatically get deleted in the function when i delete the recored in the master (M). so that i do not have to explicitly delete from each of the tables x,y,z, etc. actually i want to reinsert the records in the table x,y,z as well as M with some modification. So you want to do something like: delete from M ... Insert into M,x,y,... inside the function with the x,y, etc rows going away between those two? Hmm, yeah, that's a case that I don't think you can do currently using the on delete cascade and would require separate deletes. It'd make sense that you should be able to do that, however... It doesn't really work with how they're implemented currently, so it'd certainly be a while before it'd change in any case. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Cascading deletions does not seem to work inside PL/PGSQL functions.
On Tuesday 16 July 2002 21:41, you wrote: On Tue, 16 Jul 2002, Rajesh Kumar Mallah. wrote: Hi folks, This problem has been troubling me for quite sometime and I would be very thankful for your help. I have included the complete commented script to recreate the problem in question. The problem is inside a plpgsql function i do not see the records in the slave tables getting deleted when i delete the corresponing referenced record from the master table. But things as expected inside a Transaction at the PSQL prompt. It should get deleted, but it won't be deleted until the end of the user's sql statement (ie, not until after the function has finished). Hi thanks for the reply, Is it a bug? or is it expected , what if i do not want to fire sperate delete SQLs for the slave tables ? regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
