[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 > 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 : > > 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] 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 > 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] 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] 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 > 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 > On Wed, Mar 17, 2010 at 12:15 PM, Marcin Krawczyk > 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 > On Wed, Mar 17, 2010 at 12:24 PM, Marcin Krawczyk > 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 > On Wed, Mar 17, 2010 at 12:24 PM, Marcin Krawczyk > 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] help
Or even simpler, or easier to understand: SELECT trim(foo, '()') FROM foobar pozdrowienia / regards / salutations mk 2010/5/5 Nicholas I > 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 > >
[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 > Hello > > use a CASE statement > > http://www.postgresql.org/docs/7.4/static/functions-conditional.html > > Regards > > Pavel Stehule > > 2010/12/8 Marcin Krawczyk : > > 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 > 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 > > Hello > > use a CASE statement > > http://www.postgresql.org/docs/7.4/static/functions-conditional.html > > Regards > > Pavel Stehule > > 2010/12/8 Marcin Krawczyk : > > > 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 > > > > >
[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
Re: [SQL] Function definitions - batch update
Thanks for the hints, I'll give it a try. pozdrowienia mk 2012/2/21 Tom Lane > Marcin Krawczyk 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] 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
> 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
>
[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
> 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] 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" 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] 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 > 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 ???
Ok, thank you. pozdrowienia mk 2013/4/18 Guillaume Lelarge > 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] 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 > 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.html<http://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 >
[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.
[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] 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] 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] 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
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] 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 >
[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] 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] 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] 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] 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] 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] 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] 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 = , TG_NARGS = 0, par = What's wrong with it ?? I'm running 8.1.4 regards mk
[SQL] array variables
Hi all. Can anyone help me solve this problem ? Say I have something like this: CREATE TEMP TABLE idx (id integer, p integer, idd integer); INSERT INTO idx (id, p, idd) VALUES (3048, 1, 12043); INSERT INTO idx (id, p, idd) VALUES (3048, 2, 2321); INSERT INTO idx (id, p, idd) VALUES (3048, 5, 12002); INSERT INTO idx (id, p, idd) VALUES (3048, 6, 2387); INSERT INTO idx (id, p, idd) VALUES (3048, 13, 4301); INSERT INTO idx (id, p, idd) VALUES (3048, 16, 1022); INSERT INTO idx (id, p, idd) VALUES (3048, 19, 321); INSERT INTO idx (id, p, idd) VALUES (3049, 2, 7543); INSERT INTO idx (id, p, idd) VALUES (3050, 1, 56324); INSERT INTO idx (id, p, idd) VALUES (3050, 2, 8746); How do I put (SELECT * FROM idx) into multidimensional array variable ? (plpgsql) Is is possible at all ? regards mk
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
Re: [SQL] array variables
Thank you guys, I appreciate your help. 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 > Στις 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 >
