[SQL] hi i am gettin error when i am deleting a function from my pgadmin
Hi good morning to all…. I created some functions in my pgadmin… when I am deleting those functions from that pgadmin its giving error…i.e An ERROR has occurred ERROR:function function_name1(character varying, character varying, character varying, date, character varying) does not exist… May I know the reason y its not deleting…. Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
Re: [SQL] on connect/on disconnect
Hi, Robert, Robert Edwards wrote: > (this is my first post to this list...) Welcome here. :-) > I am wondering if Postgres, and/or SQL in general, has a facility to > run a function at connection set-up time (after a successful connection > attempt) and/or at session completion (or disconnect)? Most JDBC connection pooling implementations will do that, at least for connection and session setup. (JBoss is one of them.) > I want to pre-populate a table (actually an INSERT rule on a view) > with some user-specific data that is unlikely to change during the > session and which is "difficult" to process (ie. affects performance > to do it too often). The problem here is that the INSERT rule might be globally visible to other, concurrent users on the database. Could you explain what exactly you want to achieve, may be we find a better way to do the whole thing. > Purely session/connection-based temporary tables would also do what > I need, but temporary tables don't seem to be able to work that way. What's the exact problem with them? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] on connect/on disconnect
Hi, Robert, Robert Edwards wrote: > Or is it possible, over the same connection, to change the database > user? My understanding of the frontend/backend protocol is that this > is not allowed. Not on protocol level, but one level higher. SET SESSION AUTHORIZATION ; pg_dump uses this frequently. See the docs for more details. http://www.postgresql.org/docs/8.1/interactive/sql-set-session-authorization.html HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] on connect/on disconnect
Robert Edwards <[EMAIL PROTECTED]> writes: > Or is it possible, over the same connection, to change the database > user? My understanding of the frontend/backend protocol is that this > is not allowed. It is possible to change users. SET SESSION AUTHORIZATION ; http://www.postgresql.org/docs/8.1/interactive/sql-set-session-authorization.html Also just for completeness: http://www.securityfocus.com/bid/16650/info -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] where clause subqueries vs multiple rows results
[SIGH] I was getting the syntax wrong. Just using ON rathar than = fixed the mistake. Time to crash for the night (day?) it seems Thanks for the replies; if I hadn't've figured it out myself they would have pushed me in the right direction. -JimC -- James Cloos <[EMAIL PROTECTED]> OpenPGP: 0xED7DAEA6 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Fwd: [SQL] hi i am gettin error when i am deleting a function from my pgadmin
On 9/11/06, Penchalaiah P. < [EMAIL PROTECTED]> wrote: Hi good morning to all…. I created some functions in my pgadmin… when I am deleting those functions from that pgadmin its giving error…i.e An ERROR has occurred ERROR:function function_name1(character varying, character varying, character varying, date, character varying) does not exist… May I know the reason y its not deleting….What schema is the function in? Perhaps you need to specify the schema name in your drop statement. It helps if you supply the exact command you are typing in.== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
[SQL] case insensitive regex clause with some latin1 characters fails
Hi, I'm not sure if this is a bug or if I'm doing something wrong. I have a database encoded with ISO-8859-1, aka LATIN1. When I do something like: SELECT 'Ä' ~* 'ä'; it returns false. If i do: SELECT 'A' ~* 'a'; I get true. According to specification, both should return true. Anyone knows what the problem might be? /Ragnar ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] case insensitive regex clause with some latin1 characters fails
"=?ISO-8859-1?Q?Ragnar_=D6sterlund?=" <[EMAIL PROTECTED]> writes: > I'm not sure if this is a bug or if I'm doing something wrong. I have > a database encoded with ISO-8859-1, aka LATIN1. When I do something > like: > SELECT 'Ä' ~* 'ä'; > it returns false. Check the database's locale setting (LC_CTYPE). It has to be one that expects LATIN1 encoding. The current regex code is generally not able to deal with locale-specific behaviors in UTF8 encoding, but it should work for single-byte encodings as long as you've got the locale setting right. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] case insensitive regex clause with some latin1 characters
My environment setup as: show lc_ctype; lc_ctype - fr_CA.UTF-8 (1 row) fis=> SELECT 'Ä' ~* 'ä'; ?column? -- f (1 row) fis=> SELECT 'Ä' ilike 'ä'; ?column? -- f (1 row) I got the same result: false "=?ISO-8859-1?Q?Ragnar_=D6sterlund?=" <[EMAIL PROTECTED]> writes: I'm not sure if this is a bug or if I'm doing something wrong. I have a database encoded with ISO-8859-1, aka LATIN1. When I do something like: SELECT 'Ä' ~* 'ä'; it returns false. Check the database's locale setting (LC_CTYPE). It has to be one that expects LATIN1 encoding. The current regex code is generally not able to deal with locale-specific behaviors in UTF8 encoding, but it should work for single-byte encodings as long as you've got the locale setting right. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] text+number, find largest entry
hi, i have a table, where there is a varchar(500) column, which contains data that is strangely formatted: it starts with letters, and ends with a number. for example: xyz001 xyz002 xyz044 xyz1243 abc01 abc993 abc2342 and so on. now, for a given text-prefix (for example "xyz"), i need to find the record with the largest "numeric component". so for example, for the text-prefix "xyz", the corresponding entry would be "xyz1243". this lookup does not have to be especially fast. i realize that i could add some additional columns to this table, and store the text-part and the numeric-part separately, but first i would prefer a non-alter-table solution :) currently my only idea is to find the longest entry, check how many of them are, and then find the ones whose numeric part starts with "9" etc... ugly, but should work. are there any better ways to do it? thanks, gabor ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] text+number, find largest entry
gabor <[EMAIL PROTECTED]> writes: > i have a table, where there is a varchar(500) column, > which contains data that is strangely formatted: > it starts with letters, and ends with a number. > for example: > xyz001 > xyz002 > xyz044 > xyz1243 > abc01 > abc993 > abc2342 > now, for a given text-prefix (for example "xyz"), i need to > find the record with the largest "numeric component". I'd try using a couple of regexp_replace() calls to pull out the text prefix and number separately. Then you could group by the one and order by the other in your favorite variant of the DISTINCT ON pattern. (See weather-report example in the SELECT reference page if you have no idea what I'm talking about.) > this lookup does not have to be especially fast. Good ;-) ... otherwise changing your schema would definitely be indicated. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] on connect/on disconnect
Markus Schaber wrote: Hi, Robert, Robert Edwards wrote: (this is my first post to this list...) Welcome here. :-) I am wondering if Postgres, and/or SQL in general, has a facility to run a function at connection set-up time (after a successful connection attempt) and/or at session completion (or disconnect)? Most JDBC connection pooling implementations will do that, at least for connection and session setup. (JBoss is one of them.) I want to pre-populate a table (actually an INSERT rule on a view) with some user-specific data that is unlikely to change during the session and which is "difficult" to process (ie. affects performance to do it too often). The problem here is that the INSERT rule might be globally visible to other, concurrent users on the database. Indeed it is, but the sole reason to use a rule (instead of a straight INSERT) is that it qualifies the INSERT against the current user. Could you explain what exactly you want to achieve, may be we find a better way to do the whole thing. Basically, I have a heirarchical arrangement of users in "roles" (almost the same as the 8.1 user/group/role mechanism, but in "PUBLIC" schema space, and with various triggers etc. in play). The access controls apply conditions based on which "roles" (groups) the current user is a member of (and these users never have "super-user" privilege, so the SET SESSION AUTHORIZATION mechanism does not apply here). The heirarchy is not a "tree" structure - any role can belong to any number of other roles (have many parents), so it is a Digraph (directed graph). I have some plpgsql functions, one of which is used to determine which roles a user is in, but it is necessarily recursively called, which means it runs in non-deterministic time. (Just for completeness, I'll include that function here: create or replace function get_anc () returns setof member as ' declare rMem member; begin for rMem in select * from member where child = $1 loop return next rMem; for rMem in select * from get_anc (rMem.parent) loop return next rMem; end loop; end loop; return; end; ' language plpgsql; my intention is to re-implement this in C once I get some other logic sorted out - if anyone can see a "better" way, please let me know!) So, to cut to the short of it, I want to call this function at connection set up and "cache" the results into a "system" table that the user can't insert (or update), using an insert rule on a view: SELECT DISTINCT parent FROM get_anc (mypid); Using a non-temporary table means I can use indexes etc. properly and do O(1) lookups to quickly determine if the user has the access they need for other SQL trigger functions and rules to use. What I really need is to be able to automatically clear the users entries back out of the table when they disconnect, just in case. Looks like there is no "ON DISCONNECT" style trigger capability, so I might have to look at implementing something there as well. Purely session/connection-based temporary tables would also do what I need, but temporary tables don't seem to be able to work that way. What's the exact problem with them? Sorry - I got the "sense" of that statement the wrong way around. Temporary tables do work fine, but I need to control inserts and deletes (using views and rules) to prevent someone from giving themselves access to stuff they shouldn't. I don't think I can create a temporary table as a different user, or maybe I can with a "setuid" function? Cheers, Bob Edwards. Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] hi i am gettin error when i am deleting a function from
Can you give us the actual delete command you are issuing? You need to specify the types of the function arguments when deleting functions, for example: DELETE FUNCTION my_sum (int, int); etc. Cheers, Bob Edwards. Penchalaiah P. wrote: Hi good morning to all…. I created some functions in my pgadmin… when I am deleting those functions from that pgadmin its giving error…i.e An ERROR has occurred ERROR:function function_name1(character varying, character varying, character varying, date, character varying) does not exist… May I know the reason y its not deleting…. *Thanks & Regards* *Penchal reddy **|** Software Engineer * *Infinite Computer Solutions **|** Exciting Times…Infinite Possibilities... * *SEI-CMMI level 5 **| **ISO 9001:2000* *IT SERVICES **|** BPO * *Telecom **|** **Finance **|** **Healthcare **| **Manufacturing **|** **Energy & Utilities **|** **Retail & Distribution **|** **Government* *Tel +91-80-5193-(Ext:503)**|** Fax +91-80-51930009 **|** Cell No +91-9980012376**|**www.infics.com** * *Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at** [EMAIL PROTECTED] _**and delete this mail from your records.* Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
