Re: [SQL] Function returning setof taking parameters from another table
Marcin Stępnicki wrote: > So far the only method I can think of is to use union all with > different parametrs, like: > > select * from f_test(123) > union all > select * from f_test(124) > union all > select * from f_test(125); > > But it is not flexible, I'd like to have parameters stored in another table. > > I can also write a pl/pg function, create a string like this with > unions and EXECUTE it. However, it seems ugly to me. One method is a PL/PgSQL set-returning function that loops over the arguments, executes the function for each, and uses an inner loop to RETURN NEXT the results. Another way is to use Pg's support for expanding set-returning functions in SELECT clauses. In simple cases you can get effects similar to the use of a series of UNION operations. For example: CREATE TYPE intpair AS (first INTEGER, last INTEGER); -- -- Make a function that returns `$1' pairs of integers from 1 to $1 -- CREATE FUNCTION gsp (INTEGER) RETURNS SETOF intpair AS $$ SELECT generate_series(1,$1), generate_series(1,$1); $$ LANGUAGE SQL; -- -- Return a set of integer pairs from (1,1) to (4,4) -- SELECT gsp(4); -- -- Just run this one to see what it does. -- SELECT x, gsp(x) FROM generate_series(1,10) as x; -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to change a view's owner in postgres
Dear All, How to change a view's owner in postgres? thanks in advance: Anoop
[SQL] How to change a view's owner in postgres(is it possible?)
Dear all, We can change the owner of a tbale like this alter table tbl_year_end owner to anoop; Is it possible to change the owner name of a view through sql?
Re: [SQL] How to change a view's owner in postgres
On Fri, Aug 1, 2008 at 11:41 AM, Anoop G <[EMAIL PROTECTED]> wrote: > How to change a view's owner in postgres? ALTER TABLE view_name OWNER TO new_owner; -- 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
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'
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] Savepoints and SELECT FOR UPDATE in 8.2
I have a client application that needs: SELECT a set of records from a table and lock them for potential updates. for each record make some updates to this record and some other records in other tables call some call a function that does some application logic that does not access the database if this function is successful commit the changes for this record release any locks on this record if the function fails rollback any changes for this record release any locks for this record It would not be too much of a problem if the locks for all the records were held until all these records were processed. It would probably not be too bad if all the changes were not committed until all the records were processed. It is important that all the records are processed even when some of iterations encounter errors. I was thinking of something like this: connect to DB BEGIN SELECT * FROM table_foo where foo_state = 'queued' FOR UPDATE; for each row do [ SAVEPOINT s; UPDATE foo_resource SET in_use = 1 WHERE ...; status = application_logic_code(foo_column1, foo_column2); IF status OK THEN ROLLBACK TO SAVEPOINT s; ELSE RELEASE SAVEPOINT s; ENDIF ] COMMIT; I found a caution in the documentation that says that SELECT FOR UPDATE and SAVEPOINTS is not implemented correctly in version 8.2: http://www.postgresql.org/docs/8.2/interactive/sql-select.html#SQL-FOR-U PDATE-SHARE Any suggestions? -- 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] column names with - and (
Hi Igor,
Thanks for the info.
I am using double quotes now and getting the result that I needed.
Thank you,
Maria
On Tue, Jul 29, 2008 at 3:14 PM, Igor Neyman <[EMAIL PROTECTED]> wrote:
> Maria,
>
> Try "" (double quotes:
>
> select x1 as "IL-a", x2 as "IL-a(p30)" from abc
>
> should help.
>
> Igor
>
>
>
> --
> *From:* [EMAIL PROTECTED] [mailto:
> [EMAIL PROTECTED] *On Behalf Of *maria s
> *Sent:* Tuesday, July 29, 2008 12:07 PM
> *To:* Osvaldo Rosario Kussama; pgsql-sql@postgresql.org; Pavel Stehule;
> Ivan Sergio Borgonovo
> *Subject:* [SQL] column names with - and (
>
> Hi All,
> When I am fetching the data from a table,
> I am giving the column names with - and ( and ) like IL-a, IL-a(p30) etc..
>
> select x1 as IL-a, x2 as IL-a(p30) from abc
>
> But I am getting
>
> ERROR: syntax error at or near "-" and also t "(" , ")"
>
> Can anyone help me to fix this?
>
> Thank you,
> Maria
>
Re: [SQL] Savepoints and SELECT FOR UPDATE in 8.2
On Fri, Aug 1, 2008 at 11:02 AM, EXT-Rothermel, Peter M <[EMAIL PROTECTED]> wrote: > > I was thinking of something like this: > > connect to DB > > BEGIN > > SELECT * FROM table_foo where foo_state = 'queued' FOR UPDATE; > for each row > do [ > >SAVEPOINT s; >UPDATE foo_resource SET in_use = 1 WHERE ...; > >status = application_logic_code(foo_column1, foo_column2); > >IF status OK >THEN > ROLLBACK TO SAVEPOINT s; >ELSE > RELEASE SAVEPOINT s; >ENDIF > ] > > > COMMIT; > > I found a caution in the documentation that says that SELECT FOR UPDATE > and SAVEPOINTS is not implemented correctly in version 8.2: > > http://www.postgresql.org/docs/8.2/interactive/sql-select.html#SQL-FOR-U > PDATE-SHARE > > Any suggestions? Why not plain rollback? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
