Re: [GENERAL] Conditionnal validation for transaction
2. The short answer is No. I've got the same issue. I come from a different sql that had a CREATE VARAIBLE which was good for the session. With PostGres, I've created a sessionsettings table and a bunch of functions to get by variable and use the value. My perceived downside is that this causes a lot of calls to be made to get the data instead of setting them one time (for most items). I've been told that the table will probably be cached so it will cost very little. Michael Gould Intermodal Software Solutions, LLC 904-226-0978 Original Message Subject: [GENERAL] Conditionnal validation for transaction From: Florent THOMAS mailingl...@tdeo.fr Date: Mon, March 19, 2012 8:28 am To: pgsql-general@postgresql.org Hy all of you, 1 - Is there a way to have conditions for committing transactions like in oracle : http://www.scribd.com/doc/42831667/47/Validation-conditionnelle-de-transaction-62 2 - Is there a way to declare local variables for a SQL statement without beiing in a function? regards -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] why no create variable
text/html; charset="utf-8": Unrecognized inline: top.letterhead
Re: [GENERAL] full text search and ILIKE type clauses.
Tom, We made most of our text, varchar columns citext data types so that we could do case insensitive searches. Is this going to negate most of the index searches? It appeared to our DBA that it would be easier to use citext data type then need to use ILIKE instead? Michael Gould Intermodal Software Solutions, LLC 904-226-0978 Original Message Subject: Re: [GENERAL] full text search and ILIKE type clauses. From: Tom Lane t...@sss.pgh.pa.us Date: Sun, March 11, 2012 7:56 pm To: Tim Uckun timuc...@gmail.com Cc: pgsql-general pgsql-general@postgresql.org Tim Uckun timuc...@gmail.com writes: I want to be able to search a lot of fields using queries that use ILIKE and unfortunately many of the queries will be using the '%SOMETHING%' or '%SOMETHING' type clauses. Since indexes are useless on those I was thinking I could use tsvectors but I can't figure out how to accomplish this. Full text search is not going to help for this unless you are willing to be very lax about replicating the semantics of ILIKE. For example, ILIKE '%foo%' should match foo anywhere within a word, but FTS is not going to be able to do better than finding words that begin with foo. If you're using 9.1, you might look into contrib/pg_trgm instead. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] full text search and ILIKE type clauses.
Tim, It is my understanding that since the extention citext is available that this gives you what your asking for and at least at this point isn't going to be part of the core. Michael Gould Intermodal Software Solutions, LLC 904-226-0978 Original Message Subject: Re: [GENERAL] full text search and ILIKE type clauses. From: Tim Uckun timuc...@gmail.com Date: Mon, March 12, 2012 1:20 pm To: mgo...@isstrucksoftware.net Cc: Tom Lane t...@sss.pgh.pa.us, pgsql-general pgsql-general@postgresql.org We made most of our text, varchar columns citext data types so that we could do case insensitive searches. Is this going to negate most of the index searches? It appeared to our DBA that it would be easier to use citext data type then need to use ILIKE instead? In the same vein... Does postgres have case insensitive collations yet? Now that 9.1 supports column level collations that would be a really great option for case insensitive queries. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Call for Google Summer of Code (GSoC) 2012: Project ideas?
I would like to see 2 features which I've found useful in other SQL engines. 1. The ability to create global variables. The syntax is usually something like CREATE VARIABLE 'foo' integer; There could be 1-n of these create. 2. The ability for the system to automatically called a stored procedure called INIT if it is available. This would trigger each time a user logs in. It allow for global variables to be setup or call any other initialization processes that need to be done to setup the environment. Best Regards, Michael Gould Intermodal Software Solutions, LLC 904-226-0978 Original Message Subject: [GENERAL] Call for Google Summer of Code (GSoC) 2012: Project ideas? From: Stefan Keller sfkel...@gmail.com Date: Thu, March 08, 2012 12:40 pm To: pgsql-general List pgsql-general@postgresql.org Hi I do have a student who is interested in participating at the Google Summer of Code (GSoC) 2012 Now I have the burden to look for a cool project... Any ideas? -Stefan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] why no create variable
There seems to be CREATE everything in Postgres but it would be really nice to have a CREATE VARIABLE which would allow us to create global variables. I know there are other techniques but this would be the easiest when doing a init routine when a user logs in to the application. Best Regards Michael Gould Intermodal Software Solutions, LLC 904-226-0978 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to return the last inserted identity column value
In some languges you can use set l_localid = @@identity which returns the value of the identity column defined in the table. How can I do this in Postgres 9.1 Michael Gould Intermodal Software Solutions, LLC 904-226-0978 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Converting stored procedures from SQL Anywhere to PostGres.
I am trying to convert stored procedures from SQL Anywhere to Postgres. I am getting error ERROR: syntax error at or near return LINE 2: return integer AS ^ ** Error ** ERROR: syntax error at or near return SQL state: 42601 Character: 81 when I run this. I'm not sure what the problem is here. Any help would be appreciated. CREATE OR REPLACE FUNCTION iss.ConfigAddKey (in isscontrib.citext,pkeyname) return integer AS $BODY$ begin declare l:retval integer; declare l:id integer; if exists(select id into l:id from iss.configkeys where keyname = pkeyname) then l:retval := l:id else insert into iss.configkeys(keyname) values (pKeyname); end if; return retval end; $BODY$ LANGUAGE 'plpgsql'; Michael Gould Intermodal Software Solutions, LLC 904-226-0978 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] what Linux to run
Thanks for all of the help. I will be doing some testing in VM's this week before loading on my other server. Michael Gould Intermodal Software Solutions, LLC 904-226-0978 Original Message Subject: Re: [GENERAL] what Linux to run From: r d rd0...@gmail.com Date: Mon, March 05, 2012 5:25 am To: pgsql-general@postgresql.org pgsql-general@postgresql.org If we move to Linux, what is the preferred Linux for running Postgres on. This machine would be dedicated to the database only.=20 I'd like a recommendation for both a GUI hosted version and a non-GUI version. I haven't used Linux in the past but did spend several year s in a mixed Unix and IBM mainframe environment at the console level. I run PostgreSQL on Fedora Core 16 64bit and have never had problems, now or before. From that point of view I can recommend FC, but I don't know how it compares performance-wise to other distros. I have been using the FC series since they split from the RedHat Linux distribs at about RedHat 9, perhaps 10 years ago and have never missed anything, and seldom noticed troublesome behavior. My main criticism of FC is that the distro updates to a new version quite often, 1-2 times per year, and upgrades are seldom as smooth as they are supposed/advertised to be, but they have become much better. Beyond that, the FC series have about everything you need for development or anything else, like running PG You can use FC both with GUI and without. It comes by default with GNOME. It also has KDE, which looks (and works) similar to Windows. Both Gnome and KDE run atop X. FC has the usual Unix shells like bash (default), sh, ksh, csh, tcsh ... and if you need to connect to your host, there are several 3270 emulator available, for X and also text-mode. Two components which do not mix well with FC are Java 7 (1.7.0x) and Oracle RDBMS 11g. For Java, stay with the 1.6 series until the problems of 1.7 are fixed. If you need to use the RDBMS besides PG then FC is not your OS. Instead, look at what systems they (Oracle) support. I hope this helps you with your decision. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] what Linux to run
Our application runs on Windows, however we have been told that we can pick any OS to run our server on. I'm thinking Linux because from everything I've read, it appears to be a better on performance and there are other features like tablespaces which we could take advantage of. On our hosted solution, the application runs in a Software as a Service model and being able to keep each companies tables in their own table space would be nice. Additionally it appears that there are a lot more ways to tune the engine if we need to than under windows, plus the capability to hold more connections. If we move to Linux, what is the preferred Linux for running Postgres on. This machine would be dedicated to the database only. I'd like a recommendation for both a GUI hosted version and a non-GUI version. I haven't used Linux in the past but did spend several year s in a mixed Unix and IBM mainframe environment at the console level. Best Regards, Michael Gould Intermodal Software Solutions, LLC 904-226-0978 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problem trying to create a temp table
text/html; charset="utf-8": Unrecognized inline: top.letterhead
Re: [GENERAL] problem trying to create a temp table
text/html; charset="utf-8": Unrecognized inline: top.letterhead
Re: [GENERAL] problem trying to create a temp table
Andrew, That is acutally what the second run was supposed to be. I copied the original on instead of the second instance, but the results were the same. Michael Gould Intermodal Software Solutions, LLC 904-226-0978 Original Message Subject: Re: [GENERAL] problem trying to create a temp table From: Andrew Gould andrewlylego...@gmail.com Date: Fri, February 24, 2012 6:41 am To: mgo...@isstrucksoftware.net Cc: Richard Huxton d...@archonet.com, pgsql-general@postgresql.org pgsql-general@postgresql.org On Fri, Feb 24, 2012 at 7:36 AM, mgo...@isstrucksoftware.net wrote: How do I access it. I just did that and when I try and access it with a ERROR: relation sessionsetting does not exist LINE 1: select * from sessionsetting ^ ** Error ** ERROR: relation sessionsetting does not exist SQL state: 42P01 Character: 15 or ERROR: relation sessionsetting does not exist LINE 1: select * from sessionsetting ^ ** Error ** ERROR: relation sessionsetting does not exist SQL state: 42P01 Character: 15 Best Regards, Michael Gould Intermodal Software Solutions, LLC 904-226-0978 Original Message Subject: Re: [GENERAL] problem trying to create a temp table From: Richard Huxton d...@archonet.com Date: Fri, February 24, 2012 6:32 am To: mgo...@isstrucksoftware.net Cc: pgsql-general@postgresql.org pgsql-general@postgresql.org On 24/02/12 13:26, mgo...@isstrucksoftware.net wrote: ALL, Using 9.1.2 on Windows 7 X64 for development. I'm trying to create a temporary table used to store session variables CREATE TEMP TABLE iss.sessionsettings When I try and run this I get the following error message. ERROR: cannot create temporary relation in non-temporary schema Temp tables get their own schema, and each session (connection) gets its own temp schema. So - don't qualify them by schema. -- Richard Huxton Archonet Ltd Try to access the table without putting the table name in double quotes. Does that make a difference? Andrew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stability in Windows?
We are using it on a rather beefy server with no problems with a Win32 client/server app. There are additonal things you can do to tune the database. I've not seen any stability problems. Remember it's been several years since version 8 came out and the current version is 9.1.2. Michael Gould Intermodal Software Solutions, LLC 904-226-0978 Original Message Subject: [GENERAL] Stability in Windows? From: Durumdara durumd...@gmail.com Date: Fri, February 24, 2012 8:39 am To: pgsql-general@postgresql.org Hi! We planned to port some very old DBASE db into PGSQL. But somebody said in a developer list that he tried with PGSQL (8.x) and it was very unstable in Windows (and it have problem when many users use it). Another people also said that they used PGSQL only in Linux - and there is no problem with it, only some patches needed for speeding up writes... What is your experience in this theme? Do you also have same experience in Windows? The user number is from 20 to up 100 (concurrently). Thanks for your every idea, help, link, information about this. Regards: dd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question on Rules
text/html; charset="utf-8": Unrecognized inline: top.letterhead
[GENERAL] Question on Rules
I am creating a rule which will copy a record when it is changed to a audittable. My question is that the first column is a UUID data type with a defined as auditaccessorid uuid DEFAULT isscontrib.uuid_generate_v4() NOT NULL, Right now I've got that set to NULL to allow the parser to compile. What value should I have in here since I want a newly created UUID? CREATE RULE log_accessor AS ON UPDATE TO iss.accessor WHERE NEW.* OLD.* DO INSERT INTO iss.auditaccessor VALUES (NULL, 'C', new.loaddtlid, new.seqno, new.billable, new.payind, new.code, new.description, new.ref, new.tractororcarrierflag, new.tractororcarrierno, new.tractorpct, new.charge, new.type, new.checkdate, new.checkno, new.processed, new.itemflag, new.tractortermloc, new.cost, new.batchno, new.editdatetime, new.edituser); Best Regards, Michael Gould Intermodal Software Solutions, LLC 904-226-0978 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] question on trigger
text/html; charset="utf-8": Unrecognized inline: top.letterhead
[GENERAL] easy function or trigger to UPPER() all alpha data
text/html; charset="utf-8": Unrecognized inline: top.letterhead
[GENERAL] problems sending email to list
text/html; charset="utf-8": Unrecognized inline: top.letterhead
[GENERAL] How to write in Postgres
text/html; charset="utf-8": Unrecognized inline: top.letterhead
Re: [BUGS] [GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe
text/html; charset="utf-8": Unrecognized inline: top.letterhead
Re: [GENERAL] Why PGSQL has no developments in the .NET area?
text/html; charset="utf-8": Unrecognized inline: top.letterhead
[GENERAL] Security setup.
text/html; charset="utf-8": Unrecognized inline: top.letterhead
[GENERAL] Securing stored procedures and triggers
We are currently migrating from Sybase's ASA 9/10 to PostGres 8.2.4. One of the features that is really nice in ASA is the ability to add the attribute hidden to a Create procedure, Create function and Create trigger. Essentially what this does is encrypt the code so that if anyone or any utility gets into the database they cannot see any of the actual code. This is a great feature for protecting intellectual processing techniques. I don't know if there is anyway to do this in PostGres. Before the hidden feature was added, we had a competitor steal some of our stored procedure processing code. Is there anyway to protect this from happening in PostGres? Best Regards, Michael Gould All Coast Intermodal Services, Inc. 904-376-7030
Re: [GENERAL] Securing stored procedures and triggers
Thanks all. In the open source community there seems to be more talent to hack than in other environments. Once I told ASA to set the hidden attribute, I've not had any problems with this, at least that I've heard of. I was hoping that I'd be able to keep others out of the database totally but I can't host these applications for all of my customers. Best Regards, Michael Gould All Coast Intermodal Services, Inc. 904-376-7030 _ From: Reg Me Please [mailto:[EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Wed, 31 Oct 2007 14:26:51 -0400 Subject: Re: [GENERAL] Securing stored procedures and triggers There's not bulletproof way, in my opinion. If they copy the whole DB structure *and* the object binaries they'll have the very same functionalities! Il Wednesday 31 October 2007 16:13:23 Douglas McNaught ha scritto: mgould [EMAIL PROTECTED] writes: We are currently migrating from Sybase's ASA 9/10 to PostGres 8.2.4. One of the features that is really nice in ASA is the ability to add the attribute hidden to a Create procedure, Create function and Create trigger. Essentially what this does is encrypt the code so that if anyone or any utility gets into the database they cannot see any of the actual code. This is a great feature for protecting intellectual processing techniques. I don't know if there is anyway to do this in PostGres. Before the hidden feature was added, we had a competitor steal some of our stored procedure processing code. Is there anyway to protect this from happening in PostGres? The only bulletproof way to do this currently is to write all your stored functions in C and load them as a shared library. -Doug ---(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 -- Reg me Please ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Possible new feature
All, I'm in the process of moving to PostGres from iAnywhere's SQL Anywhere v 10. One of the neat features from ASA 10 is the ability to create proxy tables These tables can be local or remote. The purpose of a proxy table is that once create it can be used just like any other table or view. You can use it in joins, subselects, etc. ASA sees it as just a normal table. The data can be stored in any ODBC compatible file system so it makes it easy to interact with all other database. The synatx is pretty simple : CREATE EXISTING TABLE [owner.]table-name [ (column-definition, ...) ] AT location-string column-definition : column-name data-type [NOT NULL] location-string : remote-server-name.[db-name].[owner].object-name | remote-server-name;[db-name];[owner];object-name example: CREATE EXISTING TABLE blurbs ( author_id ID not null, copy text not null) AT 'server_a.db1.joe.blurbs'; Now you can acces blurbs just like any table. Best Regards, Michael Gould All Coast Intermodal Services Inc.
