[SQL] checking for the existence of a current_setting ?
Hello,
(Postgres 8.3)
I'm misusing the current settings at some places to store session
variables.
The next function retrieve such a variable, or initialized it with a
default value.
It is working as expected but performances are slow due to the exception
block.
Is there a way to make the check more smoothly, i.e. without relying on
the exception ?
maybe some undocumented internal function ?
many thanks,
Marc Mamin
CREATE OR REPLACE FUNCTION public.var_get_check(int,text)
RETURNS text AS
$BODY$
BEGIN
return current_setting('public.' || $2 || pg_backend_pid());
EXCEPTION when undefined_object then
perform set_config ('public.' || $2 || pg_backend_pid(), $1::text,
false);
return $1::text;
END ;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Re: [SQL] BINARY and BINARY VARYING datatypes in PostgreSQL
On 02.05.2011 12:46, Grzegorz Szpetkowski wrote: I know that BIT and BIT VARYING types were deleted from SQL:2003 and there are "old new" BINARY, BINARY VARYING in SQL:2008. I have two question: 1) Are these types technically the same (I mean implementation things) ? 2) Is PostgreSQL aim to support BINARY, BINARY VARYING in 9.1 and leave BIT and BIT VARYING for backward compability (for example as aliases) ? BIT and BINARY are two different data types. Let me try to explain it in an understandable way: You have CHARACTER and CHARACTER VARYING these are bind to specific encodings / charsets / collations. PostgreSQL not implemented own encodings / charsets / collation. We use what is given by libc. When you use CHARACTER or VARCHAR as datatype in your column and let us say your system is configured to store in utf8 - and your client is using latin2 - Then you tell the system that your client is using latin2 and the system will convert your input fully automatic and transparent into utf8. Same with output - it will convert it into latin2 before returning. For example German umlauts and Euro sign use 1 byte in Latin9 - In UTF-8 two bytes are used for umlauts and 3 bytes for the Euro sign. This is great and useful for text input. But - you may not want that this automatic converting happens. You may want to store something different then text in CHAR / VARCHAR columns. There are some other reasons, why you might not want this automatic converting. For this BINARY and VARBINARY is used. BINARY and VARBINARY should ignore all client and system encodings / charsets / collations and just store the data like they are and return them like they are. You can see BINARY and VARBINARY as CHAR and VARCHAR just without a binding to encodings / charsets / collations. You should be able to convert BINARY / VARBINARY text into CHAR / VARCHAR by telling which encoding is used for the binary text. As I said before PostgreSQL not yet implemented that you can have different encodings / charsets per column. PostgreSQL use one encoding for all columns. It is the encoding which you configured during initdb. Usually it is utf8 today. We support BYTEA which also ignores locale settings. http://www.postgresql.org/docs/9.0/static/datatype-binary.html "The SQL standard defines a different binary string type, called BLOB or BINARY LARGE OBJECT. The input format is different from bytea, but the provided functions and operators are mostly the same." Removing data types is such a big change that major version number should change. The release after 7.4 was named 8.0 and not 7.5. Afair there was a data type cleaning in 8.0. This means - I doubt that any data type will be removed before PostgreSQL 10.0. "PostgreSQL supports most of the major features of SQL:2008. Out of 179 mandatory features required for full Core conformance, PostgreSQL conforms to at least 160. In addition, there is a long list of supported optional features. It might be worth noting that at the time of writing, no current version of any database management system claims full conformance to Core SQL:2008." There are countries where comparative advertising is forbidden. Who says that 179 mandatory features are required for full core conformance? What is core SQL:2008? ISO / IEC 9075 is SQL Standard. It is written from agents / experts of lots of countries. Experts of the single countries coming from research institutes and companies who develop database systems (as far as there is a developing company in that country). Means theory and practice meeting each other. The question is: Is it more important to implement the theories or is it more important to listen to the users and implement what they really need? Best Regards, Susanne -- Susanne Ebrecht - 2ndQuadrant PostgreSQL Development, 24x7 Support, Training and Services www.2ndQuadrant.com -- 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] PLpgSQL variables persistance between several functions calls
Hi, Maybe calling a function from within another function would be a solution to you. Cheers, WBL On Tue, May 3, 2011 at 4:10 AM, Pavel Stehule wrote: > Hello > > no, it's not possible > > Regards > > Pavel Stehule > > 2011/5/2 Charles N. Charotti : > > Hello everybody ! > > > > I want to know if I could share PLpgSQL variables between different > > functions and within different calls just using memory (not tables or > other > > methods). > > > > If it is really possible ? > > > > Thanks in advance, > > > > Chuck > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- "Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
Re: [SQL] PLpgSQL variables persistance between several functions calls
the best way is to put all calls into one function and store values to variables.. if that is not suitable you can try the way (which im not sure if anyone uses) and it is to store values to sequences if value type is integer. for other formats you will have to do conversions. but im not sure if sequence values are stored in database or in memory. that way will make your results visible to all sessions. On Mon, May 2, 2011 at 11:43 PM, Charles N. Charotti wrote: > Hello everybody ! > > I want to know if I could share PLpgSQL variables between different > functions and within different calls just using memory (not tables or other > methods). > > If it is really possible ? > > Thanks in advance, > > Chuck > -- --- Viktor Bojović --- Wherever I go, Murphy goes with me
Re: [SQL] BINARY and BINARY VARYING datatypes in PostgreSQL
Thank you for comprehensive answer. You are right, it is better to implement what users really need in their work, but I believe that people who writing SQL Standard want to give it pragmatic and usable. Regards, Grzegorz Szpetkowski 2011/5/3 Susanne Ebrecht : > On 02.05.2011 12:46, Grzegorz Szpetkowski wrote: >> >> I know that BIT and BIT VARYING types were deleted from SQL:2003 and >> there are "old new" BINARY, BINARY VARYING in SQL:2008. I have two >> question: >> >> 1) Are these types technically the same (I mean implementation things) ? >> 2) Is PostgreSQL aim to support BINARY, BINARY VARYING in 9.1 and >> leave BIT and BIT VARYING for backward compability (for example as >> aliases) ? > > BIT and BINARY are two different data types. > > Let me try to explain it in an understandable way: > You have CHARACTER and CHARACTER VARYING these are bind to specific > encodings / charsets / collations. > > PostgreSQL not implemented own encodings / charsets / collation. > We use what is given by libc. > > When you use CHARACTER or VARCHAR as datatype in your column and let us > say your system is configured to store in utf8 - > and your client is using latin2 - > Then you tell the system that your client is using latin2 and the system > will convert > your input fully automatic and transparent into utf8. > Same with output - it will convert it into latin2 before returning. > > For example German umlauts and Euro sign use 1 byte in Latin9 - > In UTF-8 two bytes are used for umlauts and 3 bytes for the Euro sign. > > This is great and useful for text input. > > But - you may not want that this automatic converting happens. > You may want to store something different then text in CHAR / VARCHAR > columns. > There are some other reasons, why you might not want this automatic > converting. > > For this BINARY and VARBINARY is used. > BINARY and VARBINARY should ignore all client and system encodings / > charsets / collations > and just store the data like they are and return them like they are. > > You can see BINARY and VARBINARY as CHAR and VARCHAR just without a binding > to encodings / charsets / collations. > > You should be able to convert BINARY / VARBINARY text into CHAR / VARCHAR > by telling > which encoding is used for the binary text. > > As I said before PostgreSQL not yet implemented that you can have different > encodings / charsets per column. > > PostgreSQL use one encoding for all columns. It is the encoding which you > configured during > initdb. Usually it is utf8 today. > > We support BYTEA which also ignores locale settings. > > http://www.postgresql.org/docs/9.0/static/datatype-binary.html > > "The SQL standard defines a different binary string type, called BLOB or > BINARY LARGE OBJECT. The input format is different from bytea, but the > provided functions and operators are mostly the same." > > Removing data types is such a big change that major version number should > change. > > The release after 7.4 was named 8.0 and not 7.5. > Afair there was a data type cleaning in 8.0. > > This means - I doubt that any data type will be removed before PostgreSQL > 10.0. > >> "PostgreSQL supports most of the major features of SQL:2008. Out of >> 179 mandatory features required for full Core conformance, PostgreSQL >> conforms to at least 160. In addition, there is a long list of >> supported optional features. It might be worth noting that at the time >> of writing, no current version of any database management system >> claims full conformance to Core SQL:2008." > > There are countries where comparative advertising is forbidden. > > Who says that 179 mandatory features are required for full core conformance? > What is core SQL:2008? > > ISO / IEC 9075 is SQL Standard. It is written from agents / experts of lots > of countries. > Experts of the single countries coming from research institutes and > companies who > develop database systems (as far as there is a developing company in that > country). > > Means theory and practice meeting each other. > > The question is: > Is it more important to implement the theories or is it more important to > listen to > the users and implement what they really need? > > Best Regards, > > Susanne > > -- > Susanne Ebrecht - 2ndQuadrant > PostgreSQL Development, 24x7 Support, Training and Services > www.2ndQuadrant.com > > -- 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] How to realize ROW_NUMBER() in 8.3?
Thank you for the info.
I found a simple way:
==
[1] create SEQUENCE tmp start 7820;
[2]
insert into desti_table_name
select nextval('tmp'),
c1, c2... ... cN
from t1 left join t2... ... tn
where ... ...
Just for people using 8.3, this is mimic row_number.
Emi
If your table is not terribly big, you can
try something like
SELECT a.col1,a.col2, COUNT(*) as row_number
FROM yourTable a,yourTable b
WHERE a.col1 >= b.col1 -- I'm assuming col1 is primary key
GROUP BY a.col1,a.col2
ORDER BY row_number
This is pure SQL, should work in every version...
Best,
Oliveiros
- Original Message - From: "Emi Lu"
To:
Sent: Wednesday, April 20, 2011 4:45 PM
Subject: [SQL] How to realize ROW_NUMBER() in 8.3?
Hello,
ROW_NUMBER() is only ready in 8.4. For 8.3, is there a simple way to
get row_number
select row_number(), col1, col2...
FROM tableName
Thanks a lot!
Ding Ye
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884
--
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] convert in GMT time zone without summer time
Very good!
Another question:
I want to convert the string to the type TIMESTAMP WITH TIME ZONE. What do I
have to format? 'TZ' does not.
select to_timestamp('2011-03-22 14:17:00 Europe/Berlin', '-MM-DD
HH:MI:SS TZ')
---
FEHLER: Formatmuster »TZ«/»tz« werden in to_date nicht unterstützt
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/convert-in-GMT-time-zone-without-summer-time-tp4304830p4366565.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
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] convert in GMT time zone without summer time
On 05/03/2011 12:15 AM, LaraK wrote:
Very good!
Another question:
I want to convert the string to the type TIMESTAMP WITH TIME ZONE. What do I
have to format? 'TZ' does not.
select to_timestamp('2011-03-22 14:17:00 Europe/Berlin', '-MM-DD
HH:MI:SS TZ')
Just cast it to a timestamp with time zone:
select timestamptz '2011-04-22 19:17:00 Europe/Berlin';
Remember...
The value of a timestamp with time zone is always stored internally as UTC.
When a timestamp with time zone is displayed, the time zone is based on
the client's default, the "set timezone to" statement or the "at time
zone" clause in the query.
In the case of an explicit "at time zone" clause, the result becomes a
timestamp without time zone data type (that is why the previous static
example with the "at time zone" clause was a timestamp without time zone).
A timestamp with time zone is useful to identify a specific point in
time. "Bin Laden's death was announced at...", "shuttle Endeavor
launched at...", "Amazon EC2 crashed at...". Most timestamp data I
encounter is of this type.
A timestamp without time zone might be useful for data like "Breakfast
is served at 7am". Presumably a hotel chain would serve at 7am in each
hotel and not have all hotels serve at 7am corporate headquarters time.
It takes a bit of time to wrap your head around time and time zones but
it would be well worth your time to carefully read
http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html
(IIRC, you are using 8.4) a couple times.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] function timeout
im writting plsh function which will execute some shell commands and return result as varchar to database. problem is that some commands will possibly cause to large timeout or will never stop so i wanted to ask if somehow function can be autokilled if it doesn't finish in time defined for that function. -- --- Viktor Bojović --- Wherever I go, Murphy goes with me
Re: [SQL] How to realize ROW_NUMBER() in 8.3?
Emi Lu wrote:
> Thank you for the info.
>
> I found a simple way:
> ==
> [1] create SEQUENCE tmp start 7820;
> [2]
> insert into desti_table_name
> select nextval('tmp'),
>c1, c2... ... cN
> from t1 left join t2... ... tn
> where ... ...
>
> Just for people using 8.3, this is mimic row_number.
Not really... you have to reset the sequence after the select, and i'm
not sure about ordering the result.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
