[SQL] transaction isolationa level - SERIALIZABLE
Hi list, I have some problems with SERIALIZABLE isolation level, namely my users are plagued with concurrency errors. As of postgres 9.1 (which I'm running) there has been a change to SERIALIZABLE logic, unfortunately my application has not been updated to work with the new logic. I don't have an access to it's code and the only thing I can do is to report the issue to the authors. But before I do it, since I don't actually need SERIALIZABLE for my use, is it possible to have transactions always run in default READ COMMITTED mode, regardless of application level SET SESSION CHARACTERISTICS AS TRANSACTION command ... ? (like e.g in postgres 8.1 where SERIALIZABLE = READ COMMITED) regards mk
Re: [SQL] transaction isolationa level - SERIALIZABLE
That's what I thought. Thank you. regards mk 2013/5/13 Adrian Klaver adrian.kla...@gmail.com On 05/13/2013 02:22 AM, Marcin Krawczyk wrote: Hi list, I have some problems with SERIALIZABLE isolation level, namely my users are plagued with concurrency errors. As of postgres 9.1 (which I'm running) there has been a change to SERIALIZABLE logic, unfortunately my application has not been updated to work with the new logic. I don't have an access to it's code and the only thing I can do is to report the issue to the authors. But before I do it, since I don't actually need SERIALIZABLE for my use, is it possible to have transactions always run in default READ COMMITTED mode, regardless of application level SET SESSION CHARACTERISTICS AS TRANSACTION command ... ? (like e.g in postgres 8.1 where SERIALIZABLE = READ COMMITED) I don't think so: http://www.postgresql.org/**docs/9.1/interactive/config-**setting.htmlhttp://www.postgresql.org/docs/9.1/interactive/config-setting.html Furthermore, it is possible to assign a set of parameter settings to a user or a database. Whenever a session is started, the default settings for the user and database involved are loaded. The commands ALTER ROLE and ALTER DATABASE, respectively, are used to configure these settings. Per-database settings override anything received from the postgres command-line or the configuration file, and in turn are overridden by per-user settings; both are overridden by per-session settings. regards mk -- Adrian Klaver adrian.kla...@gmail.com
Re: [SQL] ALTER USER abc PASSWORD - what's going on ???
Ok, thank you. pozdrowienia mk 2013/4/18 Guillaume Lelarge guilla...@lelarge.info On Thu, 2013-04-18 at 13:21 +0200, Marcin Krawczyk wrote: I figured it out... when changing role from pgAdmin, it has a default VALID UNTIL 1970 set and after confirming changes it just made my abc user account invalid... FYI, this pgAdmin bug has been fixed. The fix will be available in the next minor release. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
[SQL] ALTER USER abc PASSWORD - what's going on ???
Hi, I can't change the pass for my user. When I invoke ALTER USER abc PASSWORD newpassword it changes ok but then I can't login with my newpassword... It says authetication failed. My pg_hba.conf has md5 entry for the ip I connect from. It used to work ok, but I accidentaly changed the password for the user abc from pgAdmin and now I can't change it back to what it was. What's going on ? regards mk
Re: [SQL] ALTER USER abc PASSWORD - what's going on ???
I figured it out... when changing role from pgAdmin, it has a default VALID UNTIL 1970 set and after confirming changes it just made my abc user account invalid... but upon connecting postgres complains about pass authetication, maybe the message should be changed ? It's 9.1 by the way. pozdrowienia mk 2013/4/18 Marcin Krawczyk jankes...@gmail.com Hi, I can't change the pass for my user. When I invoke ALTER USER abc PASSWORD newpassword it changes ok but then I can't login with my newpassword... It says authetication failed. My pg_hba.conf has md5 entry for the ip I connect from. It used to work ok, but I accidentaly changed the password for the user abc from pgAdmin and now I can't change it back to what it was. What's going on ? regards mk
[SQL] locks problem
Hi list, I've got a locking problem which prevents me from doing some alters on my tables. When I looked into pg_locks I saw a lot of entries (around 1000) with pid being NULL and almost all of them are AccessShare. Can anyone tell me what might those be and how do I get rid of them ?
Re: [SQL] locks problem
Ok I figured it out. I had a prepared transaction holding the locks. The pg_prepared_xacts was helpful. 27-11-2012 10:27, Marcin Krawczyk jankes...@gmail.com napisał(a): Hi list, I've got a locking problem which prevents me from doing some alters on my tables. When I looked into pg_locks I saw a lot of entries (around 1000) with pid being NULL and almost all of them are AccessShare. Can anyone tell me what might those be and how do I get rid of them ?
[SQL] regexp_replace behavior
Hi list,
I'm trying to use regexp_replace to get rid of all occurrences of
certain sub strings from my string.
What I'm doing is:
SELECT regexp_replace('F0301 305-149-101-0 F0302 {x1} 12W47 0635H
{tt}{POL23423423}', E'\{.+\}', '', 'g')
so get rid of whatever is between { } along with these,
but it results in:
'F0301 305-149-101-0 F0302 '
how do I get it to be:
'F0301 305-149-101-0 F0302 12W47 0635H'
??
as I understood the docs, the g flag specifies replacement of each
matching substring rather than only the first one
what am I missing ?
regards
mk
Re: [SQL] regexp_replace behavior
Yes that's exactly what I needed. Thanks a lot.
pozdrowienia
mk
2012/11/20 Alvaro Herrera alvhe...@2ndquadrant.com
Marcin Krawczyk escribió:
Hi list,
I'm trying to use regexp_replace to get rid of all occurrences of
certain sub strings from my string.
What I'm doing is:
SELECT regexp_replace('F0301 305-149-101-0 F0302 {x1} 12W47 0635H
{tt}{POL23423423}', E'\{.+\}', '', 'g')
so get rid of whatever is between { } along with these,
but it results in:
'F0301 305-149-101-0 F0302 '
how do I get it to be:
'F0301 305-149-101-0 F0302 12W47 0635H'
??
as I understood the docs, the g flag specifies replacement of each
matching substring rather than only the first one
The first \{.+\} match starts at the first { and ends at the last },
eating the {s and }s in the middle. So there's only one match and that's
what's removed.
what am I missing ?
You need a non-greedy quantifier. Try
SELECT regexp_replace('F0301 305-149-101-0 F0302 {x1} 12W47 0635H
{tt}{POL23423423}', E'\{.+?\}', '', 'g')
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
[SQL] custom_variable_classes in 9.1
Hi list,
I'm using some global variables through custom_variable_classes facility.
I've recently switched from 8.1 to 9.1
and somewhere along the line the behavior of custom_variable_classes has
changed - if the variable has not been set
for a given session invoking it (SELECT current_setting('name.variable'))
throws and error whereas it used to return 'unset' value.
Is there a way to control this behavior or maybe call it in a different way
without an error ?
If not I'll have to think of setting this at each session start, which in
turn has me asking: is there a way to call an SQL script
at each user login ?
pozdrowienia
mk
Re: [SQL] custom_variable_classes in 9.1
Ok' ve got it, I've found some comment from Tom Lane on that:
The whole custom-variable thing is being abused far beyond what the
facility was intended for, anyway. Rather than allowing variables to
spring into existence like magic, what we should have is some facility
for letting session-local variables be *declared*, complete with type
(int/real/string) and other info as needed. See the archives --- this
was discussed not too long ago.
Does anyone know if that has been accomplished in any way ?
As to login script, I can just do ALTER ROLE xxx SET name.value = 'unset'
which is all I've been asking for.
pozdrowienia
mk
2012/2/29 Marcin Krawczyk jankes...@gmail.com
Hi list,
I'm using some global variables through custom_variable_classes facility.
I've recently switched from 8.1 to 9.1
and somewhere along the line the behavior of custom_variable_classes has
changed - if the variable has not been set
for a given session invoking it (SELECT current_setting('name.variable'))
throws and error whereas it used to return 'unset' value.
Is there a way to control this behavior or maybe call it in a different
way without an error ?
If not I'll have to think of setting this at each session start, which in
turn has me asking: is there a way to call an SQL script
at each user login ?
pozdrowienia
mk
Re: [SQL] Function definitions - batch update
Thanks for the hints, I'll give it a try. pozdrowienia mk 2012/2/21 Tom Lane t...@sss.pgh.pa.us Marcin Krawczyk jankes...@gmail.com writes: I've come across a situation when I need to add some constant code to all functions in my database. Does anyone know a way to batch update all definitions ? I've got like 500 functions so doing it one by one will be time consuming. If you're feeling like a DBA cowboy, become superuser and issue a direct UPDATE against the prosrc column of pg_proc, being careful not to update rows that aren't the functions you want to hit. Slightly saner would be to read pg_proc and construct CREATE OR REPLACE FUNCTION commands that you then EXECUTE. The latter, if not done as superuser, would at least ensure you didn't accidentally break any functions you don't own. In either case, I'd practice against a test copy of the database before doing this live ... regards, tom lane
[SQL] Function definitions - batch update
Hi list, I've come across a situation when I need to add some constant code to all functions in my database. Does anyone know a way to batch update all definitions ? I've got like 500 functions so doing it one by one will be time consuming. pozdrowienia mk
[SQL] conditional aggregates
Hi list, Can anyone advise me on creating an aggregate that would take additional parameter as a condition ? For example, say I have a table like this id;value 1;45 2;13 3;0 4;90 I'd like to do something like this SELECT min_some_cond_aggregate(value,0) FROM table to get the minimal value from table greater than 0, in this case 13. I can't do SELECT min(value) FROM table WHERE value 0 as this will mess my other computations. My current solution involves a function operating on the output of array_accum from the docs, but I'm looking for more elegant solution. Is this possible at all ? I'm running 8.1. regards mk
Re: [SQL] conditional aggregates
Yeah I know but I'm having problems creating sfunc fuction for the aggregate. regards mk 2010/12/8 Pavel Stehule pavel.steh...@gmail.com Hello use a CASE statement http://www.postgresql.org/docs/7.4/static/functions-conditional.html Regards Pavel Stehule 2010/12/8 Marcin Krawczyk jankes...@gmail.com: Hi list, Can anyone advise me on creating an aggregate that would take additional parameter as a condition ? For example, say I have a table like this id;value 1;45 2;13 3;0 4;90 I'd like to do something like this SELECT min_some_cond_aggregate(value,0) FROM table to get the minimal value from table greater than 0, in this case 13. I can't do SELECT min(value) FROM table WHERE value 0 as this will mess my other computations. My current solution involves a function operating on the output of array_accum from the docs, but I'm looking for more elegant solution. Is this possible at all ? I'm running 8.1. regards mk
Re: [SQL] conditional aggregates
Thanks, it working. pozdrowienia mk 2010/12/8 Marc Mamin m.ma...@intershop.de something like ? Select min (case when X 0 then X end) HTH, Marc Mamin *From:* pgsql-sql-ow...@postgresql.org [mailto: pgsql-sql-ow...@postgresql.org] *On Behalf Of *Marcin Krawczyk *Sent:* Mittwoch, 8. Dezember 2010 14:20 *To:* Pavel Stehule *Cc:* pgsql-sql@postgresql.org *Subject:* Re: [SQL] conditional aggregates Yeah I know but I'm having problems creating sfunc fuction for the aggregate. regards mk 2010/12/8 Pavel Stehule pavel.steh...@gmail.com Hello use a CASE statement http://www.postgresql.org/docs/7.4/static/functions-conditional.html Regards Pavel Stehule 2010/12/8 Marcin Krawczyk jankes...@gmail.com: Hi list, Can anyone advise me on creating an aggregate that would take additional parameter as a condition ? For example, say I have a table like this id;value 1;45 2;13 3;0 4;90 I'd like to do something like this SELECT min_some_cond_aggregate(value,0) FROM table to get the minimal value from table greater than 0, in this case 13. I can't do SELECT min(value) FROM table WHERE value 0 as this will mess my other computations. My current solution involves a function operating on the output of array_accum from the docs, but I'm looking for more elegant solution. Is this possible at all ? I'm running 8.1. regards mk
Re: [SQL] help
Or even simpler, or easier to understand: SELECT trim(foo, '()') FROM foobar pozdrowienia / regards / salutations mk 2010/5/5 Nicholas I nicholas.domni...@gmail.com Hi, I have a table in which the data's are entered like, Example: One (1) Two (2) Three (3) I want to extract the data which is only within the parentheses. that is 1 2 3 Thank You Nicholas I
Re: [SQL] pgAgent stats
It's pgAdmin 1.10.0. I've partially identified the problem, pgAdmin log says that following query gets executed when switching to job statistics tab : SELECT jsljlgid, jslstatus, jslresult, jslstart, jslduration, (jslstart + jslduration) AS endtime, jsloutput FROM pgagent.pga_jobsteplog WHERE jsljstid = 5 ORDER BY jslstart DESC LIMIT 0 obviously the problem is LIMIT 0 clause but why it is there remains a mystery... pgAdmin bug ? a configuration issue ? regards mk 2010/3/17 Guillaume Lelarge guilla...@lelarge.info Hi, Le 16/03/2010 08:40, Marcin Krawczyk a écrit : Hi list, does anyone know the reason for pgAdmin not showing the stats neither for selected pgAgent step nor whole job ? The pga_joblog and pga_jobsteplog both get populated with data on run so I was thinking that maybe I'm missing some view associated with statistics tabs ?? Any ideas ? Which release of pgAdmin? AFAICT, 1.10 Jobs' and Steps' statistics are last run time, status, start time, stop time, and duration. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Re: [SQL] pgAgent stats
Ha, it worked :)) kind of funny though. It was set to 0 but it's supposed to not limit anything when 0 :)) a bug ? pozdrowienia / regards / salutations mk 2010/3/17 Dave Page dp...@pgadmin.org On Wed, Mar 17, 2010 at 12:15 PM, Marcin Krawczyk jankes...@gmail.com wrote: It's pgAdmin 1.10.0. I've partially identified the problem, pgAdmin log says that following query gets executed when switching to job statistics tab : SELECT jsljlgid, jslstatus, jslresult, jslstart, jslduration, (jslstart + jslduration) AS endtime, jsloutput FROM pgagent.pga_jobsteplog WHERE jsljstid = 5 ORDER BY jslstart DESC LIMIT 0 obviously the problem is LIMIT 0 clause but why it is there remains a mystery... pgAdmin bug ? a configuration issue ? Check the 'Maximum number of rows to retrieve' option on the Query tab of the Options dialog. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do
Re: [SQL] pgAgent stats
Yeah... my bad. Sorry for being a pain in the a... ;) pozdrowienia mk 2010/3/17 Dave Page dp...@pgadmin.org On Wed, Mar 17, 2010 at 12:24 PM, Marcin Krawczyk jankes...@gmail.com wrote: Ha, it worked :)) kind of funny though. It was set to 0 but it's supposed to not limit anything when 0 :)) a bug ? What makes you say that? The docs say: Maximum rows to retrieve - This option specifies the number of job and job step statistics rows to retrieve when viewing the statistics in the main browser. Unlike the statistics for other objects which normally consist of a fixed number of rows, a row is created every time a job or job step is executed. The most recent statistics will be shown. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do
Re: [SQL] pgAgent stats
Thanks for your help guys. regards mk 2010/3/17 Dave Page dp...@pgadmin.org On Wed, Mar 17, 2010 at 12:24 PM, Marcin Krawczyk jankes...@gmail.com wrote: Ha, it worked :)) kind of funny though. It was set to 0 but it's supposed to not limit anything when 0 :)) a bug ? What makes you say that? The docs say: Maximum rows to retrieve - This option specifies the number of job and job step statistics rows to retrieve when viewing the statistics in the main browser. Unlike the statistics for other objects which normally consist of a fixed number of rows, a row is created every time a job or job step is executed. The most recent statistics will be shown. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do
[SQL] pgAgent stats
Hi list, does anyone know the reason for pgAdmin not showing the stats neither for selected pgAgent step nor whole job ? The pga_joblog and pga_jobsteplog both get populated with data on run so I was thinking that maybe I'm missing some view associated with statistics tabs ?? Any ideas ? regards mk
Re: [SQL] Trapping 'invalid input syntax for integer'
Hi, I believe you're looking for invalid_text_representation. EXCEPTION WHEN invalid_text_representation THEN regards mk 2009/9/10 Mario Splivalo mario.spliv...@megafon.hr Is there a way to trap this error in plpgsql code? I have a function that accepts integer and character varying. Inside that function I need to cast that varchar to integer. Of course, sometimes that is not possible. When I run function like that, I get this errror: fidel=# select * from get_account_info_by_tan(1, 'mario'); ERROR: invalid input syntax for integer: mario CONTEXT: SQL statement SELECT user_id FROM user_tans WHERE user_tan = $1 ::bigint PL/pgSQL function get_account_info_by_tan line 8 at assignment Now, I know I could change the SELECT so it looks like: SELECT user_id FROM user_tans WHERE user_tan::varchar = $1 But, is there a way to trap above error usin EXCEPTION WHEN keyword in plpgsql? Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] skip if latter value equal
Hi list, I was wondering if it was possible for a field in SQL query to return NULL if latter value is exactly the same ? - for given ORDER BY clause, I guess. For example, query returns: xxyy 1 4 true xxyy 5 7 true xxyy 21 8 true yyzz 5 1 false yyzz 7 7 false yyzz 8 34 false I'd like the output to be: xxyy 1 4 true NULL 5 7 NULL NULL 21 8 NULL yyzz 5 1 false NULL 7 7 NULL NULL 8 34 NULL Is there any magical trick to achieve this ? regards mk
Re: [SQL] skip if latter value equal
Thanks a lot. pozdrowienia mk 2009/7/10 Pavel Stehule pavel.steh...@gmail.com Hello you can do it simply in new PostgreSQL 8.4. In older version the best way what I know is using a stored procedure, that returns table create or replace function foo() returns setof yourtablename as $$ declare r yourtablename; s yourtablename; result youratblename; first boolean = true; begin for r in select * from yourtablename loop order by ... if first then return next r; s := r; first := false; else if r.a is distinct from s.a then result.a := r.a else result.a := NULL end if; if r.b is distinct from s.b then result.b := r.b else result.b := NULL end if; if r.c is distinct from s.c then result.c := r.c else result.c := NULL end if; if r.d is distinct from s.d then result.d := r.d else result.d := NULL end if; return next result; end if; s := r; end loop; return; end; $$ language plpgsql; select * from foo(); regards Pavel Stehule 2009/7/10 Marcin Krawczyk jankes...@gmail.com: Hi list, I was wondering if it was possible for a field in SQL query to return NULL if latter value is exactly the same ? - for given ORDER BY clause, I guess. For example, query returns: xxyy 1 4 true xxyy 5 7 true xxyy 21 8 true yyzz 5 1 false yyzz 7 7 false yyzz 8 34 false I'd like the output to be: xxyy 1 4 true NULL 5 7 NULL NULL 21 8 NULL yyzz 5 1 false NULL 7 7 NULL NULL 8 34 NULL Is there any magical trick to achieve this ? regards mk
Re: [SQL] cast bool/int
This: SELECT true::integer, false::integer also works on 8.1 -- regards mk 2009/3/23 Achilleas Mantzios ach...@matrix.gatewaynet.com Στις Monday 23 March 2009 09:59:12 ο/η Zdravko Balorda έγραψε: Hi, I need a casting operator from boolean to integer, tu put in ALTER TABLE statment after USING. Sorry in the above email i meant smth like CASE WHEN column='t' THEN 1 ELSE 0 END however SELECT 't'::boolean::int; int4 -- 1 (1 row) and SELECT 'f'::boolean::int; int4 -- 0 (1 row) work in 8.3 Any ideas? Thanks. Zdravko -- Achilleas Mantzios -- 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] array variables
I know I can do one column like this : a := ARRAY(SELECT id FROM idx); but how about more than one ? Because if I try this : a := ARRAY(SELECT id, p FROM idx); I get ERROR: subquery must return only one column SQL state: 42601 regards mk
[SQL] trigger parameters, what am I doing wrong ??
Hi guys. I'm trying to pass a parameter to trigger procedure but it's not working and I have no idea why since it looks pretty ok. Here's what I do : CREATE OR REPLACE FUNCTION test_proc() RETURNS trigger AS $BODY$ DECLARE chk boolean; parinteger := TG_ARGV[0]; BEGIN RAISE NOTICE 'TG_ARGV = %, TG_NARGS = %, par = %', TG_ARGV[0], TG_NARGS, par; -- [...] some computations RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER jks_test_proc_tg AFTER UPDATE ON test_table FOR EACH ROW EXECUTE PROCEDURE test_proc(42); And here's what RAISE NOTICE looks like : NOTICE: TG_ARGV = NULL, TG_NARGS = 0, par = NULL What's wrong with it ?? I'm running 8.1.4 regards mk
[SQL] return setof record - strange behavior
Hi everybody. Can anyone enlighten me what's wrong with this function : CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer, OUT ro integer, OUT mi integer) RETURNS SETOF record AS $BODY$ DECLARE w record; cy integer := EXTRACT (YEAR FROM current_date); BEGIN FOR w IN SELECT (CASE WHEN m 12 THEN cy + 1 ELSE cy END)::integer, (CASE WHEN m 12 THEN m - 12 ELSE m END)::integer FROM generate_series(mon + 1, mon + intv) AS m LOOP RETURN next; END LOOP; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; SELECT * FROM month_year(10, 5); Why does it return empty SET ? The amount of rows is correct though I'm running 8.1.4 regards mk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] return setof record - strange behavior
The function behaves as expected when in plain SQL, only plpgsql function has the above mentioned problem. regards mk -- 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] return setof record - strange behavior
Dzieki za odpowiedz. Ciekawe ze funkcja SQL dziala bez problemu - ale
tu juz trzeba wskazac parametry OUT.
Thanks for your answer. It's curious that SQL function works as
expected - but requires OUT params.
pozdrowienia/regards
mk
2008/8/4 Pawel Socha [EMAIL PROTECTED]:
2008/8/4 Marcin Krawczyk [EMAIL PROTECTED]
Hi everybody. Can anyone enlighten me what's wrong with this function :
CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer, OUT
ro integer, OUT mi integer)
RETURNS SETOF record AS
$BODY$
DECLARE
w record;
cy integer := EXTRACT (YEAR FROM current_date);
BEGIN
FOR w IN
SELECT (CASE WHEN m 12 THEN cy + 1 ELSE cy END)::integer, (CASE
WHEN m 12 THEN m - 12 ELSE m END)::integer
FROM generate_series(mon + 1, mon + intv) AS m
LOOP
RETURN next;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
SELECT * FROM month_year(10, 5);
Why does it return empty SET ? The amount of rows is correct though
I'm running 8.1.4
regards
mk
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Hi
merlin=# CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer)
RETURNS SETOF record AS
$BODY$
DECLARE
w record;
cy integer := EXTRACT (YEAR FROM current_date);
BEGIN
FOR w IN
SELECT (CASE WHEN m 12 THEN cy + 1 ELSE cy END)::integer, (CASE
WHEN m 12 THEN m - 12 ELSE m END)::integer
FROM generate_series(mon + 1, mon + intv) AS m
LOOP
RETURN next w;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
and
merlin=# SELECT * FROM month_year(10, 5) as (x integer, y integer);
x | y
--+
2008 | 11
2008 | 12
2009 | 1
2009 | 2
2009 | 3
(5 rows)
without output params
--
--
Serdecznie pozdrawiam
Pawel Socha
[EMAIL PROTECTED]
programista/administrator
perl -le 's**02).4^-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2**y% -;^[%`-{
a%%s%%$_%ee'
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] regexp_replace
Hi all. I'd like to know whether it's possible to reverse the
behaviour of regexp_replace, meaning :
now if I do
SELECT regexp_replace ('foobarbaz', 'b..', 'X') I get 'fooXbaz' - it
replaces the string that matches given pattern with 'X', how do I
achieve the opposite - replace the string that doesn't match the
pattern ?
regards
mk
--
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] regexp_replace
thanks / dzieki
regards / pozdrowienia
mk
2008/8/1 Pawel Socha [EMAIL PROTECTED]:
2008/8/1 Marcin Krawczyk [EMAIL PROTECTED]
Hi all. I'd like to know whether it's possible to reverse the
behaviour of regexp_replace, meaning :
now if I do
SELECT regexp_replace ('foobarbaz', 'b..', 'X') I get 'fooXbaz' - it
replaces the string that matches given pattern with 'X', how do I
achieve the opposite - replace the string that doesn't match the
pattern ?
regards
mk
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
merlin=# SELECT regexp_replace ('foobarbaz', '[^b]', 'X', 'g');
regexp_replace
XXXbXXbXX
(1 row)
--
--
Serdecznie pozdrawiam
Pawel Socha
[EMAIL PROTECTED]
programista/administrator
perl -le 's**02).4^-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2**y% -;^[%`-{
a%%s%%$_%ee'
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] record type
Or maybe anyone knows how to work with record types ? How to insert
something like ('1','2','3') into a table, or split it ? Anything ?
regards
mk
Re: [SQL] record type
Nice thanks a lot.
Niezłe, dzieki.
regards
pozdrowienia
mk
2008/7/11 Pawel Socha [EMAIL PROTECTED]:
2008/7/10 Marcin Krawczyk [EMAIL PROTECTED]:
Hi. I need to know whether it's possible for a plpgsql function to accept
record type parameters ? Is there a way to accomplish that ?
I need to use something like ('1','2','3') as a parameter.
regards
mk
All about record type
http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS
section 38.3.4
but, try this ;-)
merlin=# create table your_table(col1 int, col2 varchar(12), col3 int);
CREATE TABLE
merlin=# create or replace function test_1(val_of your_table) returns void
as $$
declare
begin
insert into your_table values(val_of.col1, val_of.col2, val_of.col3);
end;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE FUNCTION
merlin=# select test_1((1,'test',2));
test_1
(1 row)
merlin=# select * from your_table ;
col1 | col2 | col3
--+--+--
1 | test |2
(1 row)
Time: 0.380 ms
--
--
Serdecznie pozdrawiam
Pawel Socha
[EMAIL PROTECTED]
programista/administrator
perl -le 's**02).4^-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2**y% -;^[%`-{
a%%s%%$_%ee'
[SQL] record type
Hi. I need to know whether it's possible for a plpgsql function to accept
record type parameters ? Is there a way to accomplish that ?
I need to use something like ('1','2','3') as a parameter.
regards
mk
[SQL] exception handling and CONTINUE
Hi all. Can anyone tell me if there's a way to use CONTINUE clause outside the loop ? An example : FOR a IN SELECT * FROM xxx LOOP INSERT INTO yyy VALUES (a.***, ..) END LOOP; EXCEPTION WHEN unique_violation THEN CONTINUE; I get an error saying I can't use CONTINUE outside of a loop. Is there a way around this ? regards mk
Re: [SQL] exception handling and CONTINUE
Thank you guys. Alvaro your idea works tha way I wanted. Why didn't I think about it ? :) regards mk
[SQL] triggers order
Hi all. Today my question is about the order triggers are fired on a table. Is there a way to determine that order? Or what interests me even more, can I adjust the order triggers are fired? Regards mk
Re: [SQL] triggers order
Thanks for a fast answer. Frankly I was hoping it would be alphabetical ;) regards mk 2008/4/18, A. Kretschmer [EMAIL PROTECTED]: am Fri, dem 18.04.2008, um 11:44:12 +0200 mailte Marcin Krawczyk folgendes: Hi all. Today my question is about the order triggers are fired on a table. Is there a way to determine that order? Or what interests me even more, can I alphabetical. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] error codes
Hi guys. Does anyone know the error code for '*currval of sequence * is not yet defined in this session*' error ? Is there one at all? I am aware of *others *code but I'd like to avoid using that. Thanks in advance. Regards mk
[SQL] connections between servers
Hi all. I was wondering if it's possible for a trigger to perform operations on a database on different server? I saw somewhere that there's a piece of software that allows conneciotns between different databases, but what about different servers? I also thought about using perl, would it be possible to connect to different server from within perl trigger? Thanks in advance. regards mk
Re: [SQL] connections between servers
Thanks a lot, I'll give it a try. regards mk 2008/4/3, Shane Ambler [EMAIL PROTECTED]: Marcin Krawczyk wrote: Hi all. I was wondering if it's possible for a trigger to perform operations on a database on different server? I saw somewhere that there's a piece of software that allows conneciotns between different databases, but what about different servers? I also thought about using perl, would it be possible to connect to different server from within perl trigger? Thanks in advance. regards mk Yes it is possible. A PL/Perl trigger is one option you have. dblink (in contrib) and dbi-link, dblink-tds, oralink and odbclink are other options at pgfoundry.org depending on your needs. There was a recent discussion in the general mailing list about this. http://archives.postgresql.org/pgsql-general/2008-03/msg01343.php -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
Re: [SQL] aggregate reverse
Yes, you got me well. That's exactly what I was trying to achieve. Thank
you.
2008/2/1, Pavel Stehule [EMAIL PROTECTED]:
Hello
I am not sure if I understand well.
On 01/02/2008, Marcin Krawczyk [EMAIL PROTECTED] wrote:
Hi all. I wolud like to know whether it is possible to reverse the
behaviour
of an aggregate? Say I have a string '1,2,3,4'. Is there a way to split
those values to records?
create or replace function unpack(anyarray)
returns setof anyelement as $$
select $1[i]
from generate_series(array_lower($1,1), array_upper($1,1)) g(i)
$$ language sql immutable;
select * from unpack(string_to_array('1,2,3,4',','));
unpack
1
2
3
4
(4 rows)
Regards
Pavel
Regards and thanks in advance.
mk
[SQL] aggregate reverse
Hi all. I wolud like to know whether it is possible to reverse the behaviour of an aggregate? Say I have a string '1,2,3,4'. Is there a way to split those values to records? Regards and thanks in advance. mk
[SQL] age() function usage
Hi all. I am trying to determine the way to pass a variable/field value to an age() function, query looks something like: SELECT age(timestamp data_zakonczenia_fakt) FROM kip_pracownicy_umowy WHERE id_pracownika = 8 data_zakonczenia_fakt being char column equal to say '1993-11-30'. Such approach won't work, can anyone tell me the way to do it? SELECT age(timestamp '1993-11-30') works great. Same story with age(timestamp '2008-01-01', timestamp '1993-11-30'). Thanks in advance mk
Re: [SQL] age() function usage
Heh, that was easy, I must have been working for too long... :) Thanks 2008/1/25, Scott Marlowe [EMAIL PROTECTED]: On Jan 25, 2008 1:06 PM, Marcin Krawczyk [EMAIL PROTECTED] wrote: Hi all. I am trying to determine the way to pass a variable/field value to an age() function, query looks something like: SELECT age(timestamp data_zakonczenia_fakt) FROM kip_pracownicy_umowy WHERE id_pracownika = 8 data_zakonczenia_fakt being char column equal to say '1993-11-30'. Such approach won't work, can anyone tell me the way to do it? SELECT age(timestamp '1993-11-30') works great. Same story with age(timestamp '2008-01-01', timestamp '1993-11-30'). You need an explicit cast: SELECT age(data_zakonczenia_fakt::timestamp) FROM kip_pracownicy_umowy WHERE id_pracownika = 8
[SQL] table column names - search
Hi all. I would like to know if there's a way to obtain a list of tables containing specified column name? Using standard LIKE '%string' syntax would be great. Regards, mk
Re: [SQL] table column names - search
Thanks a lot. 2008/1/14, Peter Eisentraut [EMAIL PROTECTED]: Am Montag, 14. Januar 2008 schrieb Marcin Krawczyk: Hi all. I would like to know if there's a way to obtain a list of tables containing specified column name? Using standard LIKE '%string' syntax would be great. SELECT table_schema, table_name FROM information_schema.columns WHERE column_name LIKE '%name%'; Add DISTINCT and other columns to taste. -- Peter Eisentraut http://developer.postgresql.org/~petere/
[SQL] temp table existence
Hi all. Is there a way to determine the existence of a TEMP TABLE? I need to check i it exists before I create it. Doing simple check on pg_class or pg_tables is not enough because there may be other such tables created in other sessions. Or maybe anyone knows the identification (apart from 'others') of error to trap it with EXCEPTION clause?
Re: [SQL] temp table existence
Thanks for the answer but it's not quite sufficient. The code supplied on his page: CREATE OR REPLACE FUNCTION ... BEGIN PERFORM 1 FROM pg_catalog.pg_tables WHERE tablename = 'xx' AND schemaname LIKE 'pg_temp%'; IF FOUND THEN TRUNCATE xx; ELSE CREATE TEMP TABLE xx(... END IF; The function does exactly what I was trying to avoid - simple check the existence of xx table in pg_tables virtualy only by it's name, it's not enough since there may be other temp tables created in seprate sessions. The only thing those temp table differ in pg_tables i schemaname, they have that suffix number and in the above mentioned function I would have to be able to retrieve this number somehow. ... WHERE tablename = 'xx' AND schemaname LIKE 'pg_temp_' || function_to_retieve_suffix() ??? ... That would work. Otherwise all temp tables by the name of xx will be truncated, which I would not like to happen since since they may still be in use. 2007/12/29, Marcin Krawczyk [EMAIL PROTECTED]: Hi all. Is there a way to determine the existence of a TEMP TABLE? I need to check i it exists before I create it. Doing simple check on pg_class or pg_tables is not enough because there may be other such tables created in other sessions. Or maybe anyone knows the identification (apart from 'others') of error to trap it with EXCEPTION clause?
Re: [SQL] temp table existence
I just realized something... my bad. It will work since TRUNCATE removes only table from current session. Thank you again. Regards
[SQL] raise exception and transaction handling
Hi,
I have a problem with transaction
handling. What I need to do is execute an INSERT command that would
not be canceled by the
RAISE EXCEPTION command in AFTER UPDATE TRIGGER. A piece of code:
BEGIN
-- some computations
bledne := (SELECT g.q_sumka('Poz.' || lps || ' - min. cena: ' || cena || ' '
|| waluta ||'; ') FROM g.m_lista WHERE idf = NEW.id);
IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf = NEW.id) THEN
RAISE EXCEPTION 'CENY NIE SPELNIAJA WARUNKOW! %', rtrim(bledne);
BEGIN
INSERT INTO g.m_proba VALUES (1,2); -- this is the operation I need to
perform but the RAISE EXCEPTION above cancels it out
PREPARE TRANSACTION 'a';
COMMIT PREPARED 'a';
END;
END IF;
I tried to do it as shown above, with PREPARE and COMMIT but it's not
working.
