[SQL] Problem in SQL Trigger
Hii have one problem in Trigger.this trigger alread workon Red Hat Linux 7.3 but now i shift to RHL9.0in RHL 9.0 notworking . in this problem in ROUND function.but how to i slove thisi dont know. so please help me.This is Function andTrigger./// Function Start//CREATEFUNCTION add_issue_fun() returns opaque as'BEGINUPDATEmtrl_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_csWHERE rcpt_detail.batch_code = NEW.batch_codeand rcpt_detail.mtrl_code = NEW.mtrl_codeandrcpt_detail.loc_code = NEW.loc_code;returnNULL;END;'language 'plpgsql';/// Function End/Trigger Start //CREATETRIGGER add_issue_trgAFTER INSERT ON issue_detailFOR EACHROW EXECUTE PROCEDURE add_issue_fun();/// Trigger End//ThankingRamesh Patel Computer Dept. Banasdairy, Palanpur
Re: [SQL] Multi ordered select and indexing
Have you thought of using a functional index on both columns? Regards, Christoph > > Hi! > What is the simplest solution for this query type: > > SELECT * FROM tablename ORDER BY col1 ASC, col2 DESC; > > In our experience, postgres cannot use a multi-colum index on (col1, > col2) in this situation. Is custom operator class the easiest solution, > which can solve the reverse indexing on col2? Our problem with this > solution, is that we have to replace "DESC" with "USING myoperator". Is > it possible, that postgres can recognize "myoperator" without replacing > "DESC"? > We made new operators on int4 type starting with letter "/": > > CREATE OPERATOR CLASS int4_reverse_order_ops > FOR TYPE int4 USING btree AS > OPERATOR1 /< , > OPERATOR2 /<= , > OPERATOR3 /= , > OPERATOR4 />= , > OPERATOR5 /> , > FUNCTION1 int4_reverse_order_cmp(int4, int4); > > Create an index: > CREATE INDEX idx_test ON tablename (col1, col2 > int4_reverse_order_ops); > > Postgres use this index in this query: > EXPLAIN SELECT * FROM tablename ORDER BY col1 ASC, col2 USING /< limit > 10; > QUERY PLAN > > > Limit (cost=0.00..0.52 rows=10 width=8) >-> Index Scan using idx_test on tablename (cost=0.00..52.00 > rows=1000 width=8) > > Another problem: we have to replace the operators in WHERE conditions, > if that contains condition on col2. > EXPLAIN SELECT * FROM tablename WHERE col1 < 10 and col2 < 10 ORDER BY > col1, col2 using /< limit 10; > QUERY PLAN > > --- > Limit (cost=0.00..4.14 rows=10 width=8) >-> Index Scan using idx_test on tablename (cost=0.00..46.33 > rows=112 width=8) > Index Cond: (col1 < 10) > Filter: (col2 < 10) > > You can see, it use filtering on col2, but in the next case it can > indexing on col2 condition: > EXPLAIN SELECT * FROM tablename WHERE col1 < 10 and col2 /< 10 ORDER BY > col1, col2 using /< limit 10; > QUERY PLAN > > --- > Limit (cost=0.00..3.82 rows=10 width=8) >-> Index Scan using idx_test on tablename (cost=0.00..42.78 > rows=112 width=8) > Index Cond: ((col1 < 10) AND (col2 /< 10)) > > Can we do this easier? If can, how? > > After that, we have an other unsolved problem, if the col2's type is > TEXT, and we try to use a LIKE operator on it. We coludn't replace the > LIKE with own operator, because postgres exchange the "LIKE" with an > expression which contains ">=" and "<". We made own like operator: > "/~~", but we cannot tell postgres to use our own "/>=" and '/<' > operators instead of "/~~". > CREATE OPERATOR /~~ ( >leftarg = text, rightarg = text, procedure = textlike, >commutator = /~~ , negator = !~~ , >restrict = scalarltsel, join = scalarltjoinsel > ); > > Thanks in advance. > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Equivalant of SQL Server's Nchar and NVARCHAR
Dear friends, Is there any equivalent datatype of SQL Server's NCHAR and NVARCHAR, available with Postgres 7.3.4. I want to store characters, special characters and Numbers. Please shed some light. Thanks Kumar
Re: [SQL] Equivalant of SQL Server's Nchar and NVARCHAR
> Dear friends, > Is there [...] > Please shed some light. http://www.postgresql.org/docs/7.4/static/index.html > Thanks You are welcome. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Equivalant of SQL Server's Nchar and NVARCHAR
kumar wrote:
Dear friends,
Is there any equivalent datatype of SQL Server's NCHAR and NVARCHAR,
available with Postgres 7.3.4. I want to store characters, special
characters and Numbers.
Please shed some light.
Thanks
Kumar
I only only use ascii but...
I believe postgresql varchar is the same as Oracle/Sybase/MS SQL
nvarchar even though it doesn't explicitly say so here:
http://www.postgresql.org/docs/7.4/static/datatype-character.html
Here is a short example:
create table
nvctest (
utf8fld varchar(12)
);
insert into nvctest
select convert('PostgreSQL' using ascii_to_utf_8);
select * from nvctest;
text functions including encoding conversions are here:
http://www.postgresql.org/docs/current/static/functions-string.html
and
http://www.postgresql.org/docs/current/static/functions-string.html#CONVERSION-NAMES
Good Luck,
Rob
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Use arrays or not?
Roelant,
Yours is not a performance question, so I'm crossing it over to SQL for advice
on database design.
> I am building an application using postgresql to store XML-records. There
> is a debate within the group of developers about the best way to store our
> data. I hope you can help us make a decision.
>
> The data consists of XML-records, with a lot of XML-fields. I want to store
> the XML as it is, so taking the information from the XML-records and then
> storing it in a different-from-XML-format is not an option.
>
> Each XML-record describes data about one book. If an update of bookdata
> comes, the XML itself is not changed, but a new XML-record is stored with
> the updated data. Via a complex scheme of combining a base record and its
> updates, the final dataset is produced that is used in the application.
>
> There are different XML-formats that need to be combined. Right now, we can
> handle three different XML-formats, each with its own structure (but all
> describing book-data).
>
> Searching is done via a simple table lookup on three different fields:
> title, author and subject. The data for these fields is extracted from the
> database. Each book has a unique identifier (EAN13, derivative of ISBN).
>
> Here is one way to organize the database:
> table title:
> TITLE | EAN13, indexing on TITLE
>
> table author:
> AUTHOR | EAN13, indexing on AUTHOR
>
> table subject:
> SUBJECT | EAN13, indexing on SUBJECT.
This is a *very* strange way of setting up your database. Are you new to
Relational Databases and SQL? If so, I'd recommend starting with a book on
relational database design.
Either that, or you're a victim of UML design.
If only one author, title and subject are allowed per book, you should have:
table books
EAN13 | TITLE | AUTHOR | SUBJECT
> Finally:
> table record:
> EAN13 | ARRAY OF XML-records.
>
> It's the last table that I am most curious (and worried) about, the
> question being mainly what the optimal way of structuring that table is.
> Option 1 is the given option: adding/deleting an XML-record for the same
> book requires adding/deleting it to/from the array of XML-records.
>
> Option 2 would be something like this:
> EAN13 | XML-record
> where, if a book has several records describing it, there are multiple
> entries of the EAN13|XML-record - pair. Adding an XML-record for the same
> book, requires adding a new entry to the table as a whole.
In my mind, there is no question that this is the best way to do things. It
is a normalized data structure, as opposed to the arrays, which are now.
>
> So, option 1-tables look like this:
> EAN13 | ARRAY OF XML-records
> 0001 | {..., ..., ...}
> 0002 | {..., ..., ...}
>
> Option-2 tables look like this:
> EAN13 | ARRAY OF XML-records
> 0001 | ...
> 0001 | ...
> 0002 | ...
> 0002 | ...
>
> We can't decide which one is best. These are some issues we can think of:
>
> Indexing: For option 1, the EAN13-index remains unique, even if you have
> multiple XML-records; for option 2 it does not, since multiple XML-records
> are stored as multiple tuples. On the other hand, an additional internal
> index can be used to link the several tuples of option 2 to the information
> in the `lookup'-tables (author, title, keyword). Does any of these two
> options increase query efficiency, ie. speed?
>
> Database growth: On average, the information about a book is updated three
> times per year. In option 1, this means that the length of the table does
> not increase, but the width does. If we choose option 2, if we have three
> updates per book each year, the length of the table triples, but the width
> does not. What is more costly to store for postgres, long arrays or long
> tables?
>
> Integrity: Option 1 means that our software needs to keep track of all the
> bookkeeping for arrays, since such support is quite rudimentary in
> postgres. For example, it is hard to take out a record from the middle of
> an array. Also, a multidimensional array, which contains for each record
> the record itself and its type, is even harder to maintain. Option 2 has a
> simpler datatype, so integrity can be easier inforced using the standard
> postgres-machinery of variable-types etc.
>
> Arrays are non-standard SQL, and I hear that PHP-support for postgres &
> arrays is rudimentary. So that might be an argument to avoid using them,
> and go for option 2. From the standpoint of performance (or wisdom), can
> you help me decide what I should choose? Or is there maybe an even better
> way to structure my data?
>
> Thanks for any contribution!
>
> Roelant.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Check a value in array
Hi all. I have to check if a value is in an array. I've got a date array in a table and I would like to perform queries like: SELECT * FROM table WHERE date IN dates_array; I've tried using array_contains_date (contrib/array_iterator.sql) function unsuccessfully: SELECT array_contains_date(dates_array, date); WARNING: plpgsql: ERROR during compile of array_contains_date near line 5. ERROR: missing .. at end of SQL expression Anyone can help me? Thanks, Marco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Check a value in array
On Thu, 2004-04-29 at 13:37, Marco Lazzeri wrote: > Hi all. > > I have to check if a value is in an array. > > I've got a date array in a table and I would like to perform queries > like: > > SELECT * FROM table WHERE date IN dates_array; If you're using 7.4 or later, try: SELECT * FROM table WHERE date = ANY(dates_array); This will work without the contrib package. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Equivalant of SQL Server's Nchar and NVARCHAR
Kumar, > I only only use ascii but... > I believe postgresql varchar is the same as Oracle/Sybase/MS SQL > nvarchar even though it doesn't explicitly say so here: All of our TEXT datatypes are multibyte-capable, provided you've installed PostgreSQL correctly.This includes: TEXT (recommended) VARCHAR CHAR -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Multi ordered select and indexing
"Antal Attila" <[EMAIL PROTECTED]> writes: > CREATE OPERATOR CLASS int4_reverse_order_ops > FOR TYPE int4 USING btree AS > OPERATOR1 /< , > OPERATOR2 /<= , > OPERATOR3 /= , > OPERATOR4 />= , > OPERATOR5 /> , > FUNCTION1 int4_reverse_order_cmp(int4, int4); This is the wrong way to go about it. A useful descending-order opclass simply rearranges the logical relationships of the standard comparison operators. You do need a new comparison function, but nothing else: CREATE OPERATOR CLASS int4_reverse_order_ops FOR TYPE int4 USING btree AS OPERATOR1 > , OPERATOR2 >= , OPERATOR3 = , OPERATOR4 <= , OPERATOR5 < , FUNCTION1 int4_reverse_order_cmp(int4, int4); Now you can just use ASC/DESC in your ORDER BY ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Permissions not working
Hi , I am having some problems with setting up permissions in Postgres. I have a database for ex: 'ups' and it was owned previously by 'postgres(superuser)' but now i have changed the ownership to new user 'ups' all the tables are owned by these user 'ups'. This database doesnt have any schemas except for 'Public'. I have created another user lets say 'test' and i didnt give 'test' user any permissions to access the tables owned by 'ups' but still when i login to 'ups' database as psql ups test and run a select on the tables owned by 'ups' database it goes through. I dont want user 'test' to access any tables from the 'ups' database, i tried revoking permissions it still doesnt work. Can anyone tell me what is wrong here ? Appreciate your help. Thanks! Pallav ---(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] Use arrays or not?
Roelant, > So, let me rephrase my questions: > 1. When and why would anyone use arrays? When the data itself is an ordered set of items which is indivisible and lacks meaning outside the ordered set. For example, a set of ordered pairs of molecules in a gene snippet. Or a mathematical matrix. > 2. When designing the database, is it really true that there is no performance > difference between a table of which the number of tuples grow by a factor of, > say 10, and a table of which the size of the tuples grow by a factor of, say > 10? Nobody's tested anything. I would *tend* to think that PostgreSQL would handle more-of-less-wide-rows somewhat better, but that's just a guess. Hmmm ... not completely a guess. Postgres, by default, compresses fields over 8K in size (see TOAST in the docs). This makes those fields somewhat slower to update. So if 1 XML rec < 8k but 4 XML rec > 8k, there could be a small-but-noticeable performance loss from going to "broad" rows. If I had your application, I would not go for the array approach, jjust to avoid maintainence headaches.For example, what happens when the books start having a variable number of XML records? Normalized designs are almost always easier to deal with from a perspective of long-term maintainence. The arrays, as far as I can tell, gain you nothing in ethier performance or convenience. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Permissions not working
On Thu, 29 Apr 2004, Pallav Kalva wrote: > Hi , > > I am having some problems with setting up permissions in Postgres. I > have a database for ex: 'ups' and it was owned previously by > 'postgres(superuser)' but now i have changed the ownership to new user > 'ups' all the tables are owned by these user 'ups'. This database doesnt > have any schemas except for 'Public'. I have created another user lets > say 'test' and i didnt give 'test' user any permissions to access the > tables owned by 'ups' but still when i login to 'ups' database as psql > ups test and run a select on the tables owned by 'ups' database it > goes through. >I dont want user 'test' to access any tables from the 'ups' > database, i tried revoking permissions it still doesnt work. Can anyone > tell me what is wrong here ? Log in as the superuser (usually postgres) and see what you get from this query: select usesuper from pg_shadow where usename='test'; if usesuper is t, then test is a superuser and can do anything he wants. You need to issue the command: alter user test with nocreateuser; If that isn't the problem, let us know. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Permissions not working
scott.marlowe wrote: On Thu, 29 Apr 2004, Pallav Kalva wrote: Hi , I am having some problems with setting up permissions in Postgres. I have a database for ex: 'ups' and it was owned previously by 'postgres(superuser)' but now i have changed the ownership to new user 'ups' all the tables are owned by these user 'ups'. This database doesnt have any schemas except for 'Public'. I have created another user lets say 'test' and i didnt give 'test' user any permissions to access the tables owned by 'ups' but still when i login to 'ups' database as psql ups test and run a select on the tables owned by 'ups' database it goes through. I dont want user 'test' to access any tables from the 'ups' database, i tried revoking permissions it still doesnt work. Can anyone tell me what is wrong here ? Log in as the superuser (usually postgres) and see what you get from this query: select usesuper from pg_shadow where usename='test'; if usesuper is t, then test is a superuser and can do anything he wants. You need to issue the command: alter user test with nocreateuser; If that isn't the problem, let us know. Thanks! for the quick reply, I ran the above query and it is 'f' for the 'test' user, 'test' is not a super user. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Permissions not working
On Thu, 29 Apr 2004, Pallav Kalva wrote: > scott.marlowe wrote: > > >On Thu, 29 Apr 2004, Pallav Kalva wrote: > > > > > > > >>Hi , > >> > >>I am having some problems with setting up permissions in Postgres. I > >>have a database for ex: 'ups' and it was owned previously by > >>'postgres(superuser)' but now i have changed the ownership to new user > >>'ups' all the tables are owned by these user 'ups'. This database doesnt > >>have any schemas except for 'Public'. I have created another user lets > >>say 'test' and i didnt give 'test' user any permissions to access the > >>tables owned by 'ups' but still when i login to 'ups' database as psql > >>ups test and run a select on the tables owned by 'ups' database it > >>goes through. > >> I dont want user 'test' to access any tables from the 'ups' > >>database, i tried revoking permissions it still doesnt work. Can anyone > >>tell me what is wrong here ? > >> > >> > > > >Log in as the superuser (usually postgres) and see what you get from this > >query: > > > >select usesuper from pg_shadow where usename='test'; > > > >if usesuper is t, then test is a superuser and can do anything he wants. > >You need to issue the command: > > > >alter user test with nocreateuser; > > > >If that isn't the problem, let us know. > > > > > Thanks! for the quick reply, I ran the above query and it is 'f' for the > 'test' user, 'test' is not a super user. Ok, then what does \z tablename where tablename is one of the tables you don't want test to access. ---(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] isnumeric() function?
What is isnumeric function in postgresql? I'm using psql version 7.2.2 thanks Yudie ---(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] Permissions not working
Pallav Kalva <[EMAIL PROTECTED]> writes: > I have a database for ex: 'ups' and it was owned previously by > 'postgres(superuser)' but now i have changed the ownership to new user > 'ups' all the tables are owned by these user 'ups'. That isn't a supported operation. How did you do it exactly? I suspect that you got it wrong somehow ... > I dont want user 'test' to access any tables from the 'ups' > database, i tried revoking permissions it still doesnt work. What did you revoke? What does psql's "\z" command show for the problem tables? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
