[SQL] calling EXECUTE on any exception
Hi all: I want to call some FUNCTION , let say exception_hanler(Exception_code) for any exception in plsql functions. I give some pseudo code to explain myself better: CREATE FUNCION something(...) returns ... AS ' ... EXCEPTION WHEN OTHER THEN EXECUTE exception_handler(Exception_code) ... ' and let exception_handler() function make all the work and returns some value for mi program...Now, the actual question...how do i get that error_code (or code_name, whatever i can get) generated in the something() function to be proccesed by the exception_handler() function? Im talking of 8.0 plsql language by the way. Thanks in advance! -- Gerardo Herzig Direccion General de Organizacion y Sistemas Facultad de Medicina U.B.A. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] calling EXECUTE on any exception
Oh...to bad...Thank you Michael! Did someone know if it can be acomplished in pypgsql? Thanks againg falks. Gerardo > On Fri, Jul 29, 2005 at 10:36:52AM -0300, [EMAIL PROTECTED] wrote: >> EXCEPTION >> WHEN OTHER THEN >> EXECUTE exception_handler(Exception_code) >> >> how do i get that error_code (or code_name, whatever i can get) >> generated in the something() function to be proccesed by the >> exception_handler() function? > > I don't think you can do this in released versions of PostgreSQL. > In 8.1 you'll be able to use SQLSTATE to get the error code and > SQLERRM to get the error message. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] [SOT] pypgsql function receiving dictionary as parameter?
Hi all. Im a python programer, and im trying to use a dictionary (associative array on Perl) in my pypgsql function without result. Anybody know if it is possible? Thanks a lot folks. -- Gerardo Herzig Direccion General de Organizacion y Sistemas Facultad de Medicina U.B.A. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [SOT] pypgsql function receiving dictionary as parameter?
Suppose this python structure:
someDict = {
'field1': 'Foo',
'creepyfield': 'Bar'
}
and the connection to the database
CONN = pg.connect()
I want some pypgslq function
CREATE myfunction ( __dictionary__) returns void
AS
(process)
LANGUAGE pythonu
and (the desired goal) be able to execute
CONN.execute("select * from myfunction (someDict)")
Thanks again, and yes, i might post to the others postgres list too.
--
Gerardo Herzig
Direccion General de Organizacion y Sistemas
Facultad de Medicina
U.B.A.
> On Fri, Aug 19, 2005 at 10:13:20AM -0300, [EMAIL PROTECTED] wrote:
>> Hi all. Im a python programer, and im trying to use a dictionary
>> (associative array on Perl) in my pypgsql function without result.
>> Anybody
>> know if it is possible?
>
> Please show a minimal but complete example of what you're trying to do.
>
> BTW, pgsql-sql is supposed to be for SQL-related matters; this thread
> would be more appropriate in pgsql-general or pgsql-interfaces.
>
> --
> Michael Fuhr
>
>
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] refer a column as a varible name?
Hi all. I have troubles trying to achieve this assignment: suppose the type mycolumn as (field1, varchar, field2 varchar) and field_name = ''field1'' and returnValue declared as mycolumn ... can i say returnValue.$field_name = ''ok''? There is a way to achieve this piece of code? Thanks a lot! -- Gerardo Herzig Direccion General de Organizacion y Sistemas Facultad de Medicina U.B.A. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] refer a column as a varible name?
Im using pg 8.0. I personally dont have problem in using pl/perl, but none
of my companions is a perl programmer, so i guess the IF..THEN seems to be
an easy (altough not preferable) solution.
Thank you very much Michael.
Gerardo
> On Mon, Sep 12, 2005 at 12:21:22PM -0300, [EMAIL PROTECTED] wrote:
>> suppose the
>>
>> type mycolumn as (field1, varchar, field2 varchar)
>> and
>>
>> field_name = ''field1''
>>
>> and returnValue declared as mycolumn
>> ...
>> can i say returnValue.$field_name = ''ok''?
>
> To achieve this in PL/pgSQL you'll need to use a conditional statement
> (IF field_name = 'field1' THEN ...). I'm not sure if a solution
> involving EXECUTE is possible; if so then it's probably non-obvious.
>
> What version of PostgreSQL are you using, and do you have a requirement
> to use PL/pgSQL? In 8.0 PL/Perl can return composite types and such
> an assignment would be trivial:
>
> CREATE TYPE mycolumn AS (field1 varchar, field2 varchar);
>
> CREATE FUNCTION foo(varchar) RETURNS mycolumn AS $$
> my $field_name = $_[0];
> my $returnValue = {$field_name => "ok"};
> return $returnValue;
> $$ LANGUAGE plperl IMMUTABLE STRICT;
>
> SELECT * FROM foo('field1');
> field1 | field2
> +
> ok |
> (1 row)
>
> SELECT * FROM foo('field2');
> field1 | field2
> +
> | ok
> (1 row)
>
> --
> Michael Fuhr
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
>
>
--
Gerardo Herzig
Direccion General de Organizacion y Sistemas
Facultad de Medicina
U.B.A.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] problem using regexp_replace
Hi all. Im having a hard time here. Really have no idea what is wrong here.
Facing a special case of text substitution, i have to parse a column like
this one:
SELECT formato from table where id=1;
{Action_1.842}{Action_2.921}[truncated]
The numbers at the rigth of the period identifies an argument to the
function identified to "Action_x"
Every {Action_x} is asociated to a diff function , so i have a helper
function to identify the "Action" part:
CREATE FUNCTION valores_sustitucion(valor_ingresado varchar[])
returns varchar
as
$$
select case
$1[1] when 'Action_1' then
(select descripcion from load_by_cod($1[2]))
when 'Action_2' then (select descripcion from pay_by_view($1[2])
else 'FALSE'
end;
$$ language sql;
So, the idea is, to call associated function with every "Action_x", with
the number as the argument to that associated function.
So, i come with this:
SELECT regexp_replace(
formato, E'{([^.]*)\.([a-zA-Z0-9]*)},
valores_sustitucion(ARRAY[E'\\1'::varchar,E'\\2'::varchar]),
'g')
from table where id =1;
FALSEFALSE
The valores_sustitucion() functions is called, but the case construction
is failing. I have tested the regular expression, and its fine.
It looks like is something with the grouping and using that groups as the
argument of the valores_sustiticion() funcion.
Anybody has a hint?
Thanks!
Gerardo
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] problem using regexp_replace
> On 2010-01-11, [email protected] wrote: > >> CREATE FUNCTION valores_sustitucion(valor_ingresado varchar[]) >> returns varchar >> as >> $$ >> select case >> $1[1] when 'Action_1' then >> (select descripcion from load_by_cod($1[2])) >> >>when 'Action_2' then (select descripcion from pay_by_view($1[2]) >> >> else 'FALSE' >> end; >> $$ language sql; > >> Anybody has a hint? > > you are missing a ) > Oh, thats a copy-paste problem, sory about that. I forgot to mention, this is a 8.3 running on linux. Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] problem using regexp_replace
> On 2010-01-11, [email protected] wrote: > >> So, i come with this: >> SELECT regexp_replace( >> formato, E'{([^.]*)\.([a-zA-Z0-9]*)}, >> valores_sustitucion(ARRAY[E'\\1'::varchar,E'\\2'::varchar]), >> 'g') >> from table where id =1; > > select valores_sustitucion(ARRAY[E'\\1'::varchar,E'\\2'::varchar]); > > valores_sustitucion > - >FALSE > > that's the problem you are getting, the valores_sustitucion works on > the values given and that result is given to regexp_replace. > > try this: > > create OR REPLACE function magic( inp text ) returns text as $F$ > DECLARE > tmp text; > res text; > BEGIN > tmp= 'SELECT ' || > regexp_replace(quote_literal(inp),E'{([^.]*)\.([a-zA-Z0-9]*)}', > $s$'|| valores_sustitucion(ARRAY[E'\1',E'\2']) ||'$s$,'g'); > --raise notice 'tmp=%',(tmp); > EXECUTE tmp INTO res; > RETURN res; > END; > $F$ language plpgsql; > > SELECT magic( formato ) FROM from table where id =1; > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > You hit that really hard, Jasen, thank you very much!! You save my week :) Thanks again. Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] best index for ~ ordering?
Well, im wondering if is possible using LIKE '%blah%', even better would be upper/lower(string) like '%blah%', Im not at work right now, i will try it latter and makes you know about the results of using tsearch indexing. Thanks a lot, man! Gerardo > On Tue, Mar 06, 2007 at 06:34:23PM -0300, Gerardo Herzig wrote: >> Hi all, i have a large table with one varchar field, and im triyng to >> get information about what index should i use in order to using that >> index (if this is possible) for ~ searching, and/or using LIKE >> searching. > > What sort of match are you doing? If you're doing left-anchored > searches (LIKE 'blah%') then your standard btree is good. If you're > doing unanchored searches (LIKE '%blah%' or similar) you're best doing > tsearch. If it's right-anchored, you can do an index on the reverse > of the string. > > A > > -- > Andrew Sullivan | [EMAIL PROTECTED] > Unfortunately reformatting the Internet is a little more painful > than reformatting your hard drive when it gets out of whack. > --Scott Morris > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > > -- Gerardo Herzig Direccion General de Organizacion y Sistemas Facultad de Medicina U.B.A. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] index not being used. Why?
Thanks all you guys. Indeed, populating the tables with 10.000 entrys make the things different, and now it uses all the indexes as i spect. It was just a matter of being pacient and loading more data to test it out and see. And, yes, i need to upgrade psql now. Actually the real server has an 8.2.0 engine. Thanks all you guys! Gerardo > On Fri, 2007-03-09 at 09:01, Gerardo Herzig wrote: >> Hi all. I have this 2 relations >> > > SNIP > >>Index Cond: ((upper((word)::text) ~>=~ 'TEST'::character >> varying) AND (upper((word)::text) ~<~'TESU'::character >> varying)) >>-> Hash (cost=9.08..9.08 rows=408 width=55) >> -> Seq Scan on pages (cost=0.00..9.08 rows=408 width=55) >> >> (8 rows) >> >> >> Watch the last row of the explain command. It makes a sequential scan >> on the pages table, like it is not using the index on the "id" field. >> >> The result itself is OK, but i will populate the tables so i think >> that later that sequential scan would be a problem. > > Welcome to the world of tomorrow! hehe. PostgreSQL uses a cost based > planner. It decided that an index would cost more than a seq scan, so > it chose the seq scan. As mentioned in other posts, you'll need to do > an analyze. Also, look up things like vacuum / autovacuum as well. > >> Im using postgres 8.1.3 > > You need to upgrade to 8.1.8 or whatever the latest version is by the > time this email gets to you :) 8.1.3 is about a year out of date. > > > -- Gerardo Herzig Direccion General de Organizacion y Sistemas Facultad de Medicina U.B.A. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] foreign key pointing to diff schema?
Hi all. Can i make a FK who points a table in a different schema? Or this is implemented via a trigger by my own? Thanks! Gerardo ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] foreign key pointing to diff schema?
> On 8/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> Hi all. Can i make a FK who points a table in a different schema? Or >> this >> is implemented via a trigger by my own? > > Sure. just prefix the table name with the schemaname and a . > > create schema abc; > alter user me set search_path='abc', 'public'; > create table z1 (id int primary key); > \d z1 > Table "abc.z1" > Column | Type | Modifiers > +-+--- > id | integer | not null > Indexes: > "z1_pkey" PRIMARY KEY, btree (id) > > (Note the abc.z1 there) > create schema test3; > create table test3.z2 (id int primary key, z1id int references > abc.z1(id)); > \d test3.z2 >Table "test3.z2" > Column | Type | Modifiers > +-+--- > id | integer | not null > z1id | integer | > Indexes: > "z2_pkey" PRIMARY KEY, btree (id) > Foreign-key constraints: > "z2_z1id_fkey" FOREIGN KEY (z1id) REFERENCES z1(id) > > > basically, schemas are just distinct name spaces. > Im sory, that was just a matter of trying and see, isnt? I guess i need a beer :) Thanks for the example and the explanation! Gerardo ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] reading WAL files in python
> > On Jan 7, 2008, at 7:19 AM, Gerardo Herzig wrote: > >> Hi all. Im having some fun trying to write my own replication >> system using python. I will use the postgres own WAL archiving to >> write the files, then my app will read them and do some stuff. As >> im not a C programmer, im stuck in the mission of reading the >> binary files. >> >> I guess im needing to know the internals of how wals archives are, >> how big the buffer has to be, in order to have a complete sql command. >> >> Can someone point some advice? >> I was trying to understad src/backend/access/transam/xlog.c, but >> seems too difficult to me :( > > The biggest problem with what you're wanting to do here is that the > data written to the WALL archives isn't going to be at all easily > translatable into SQL statements. AFAIK, the WAL data records on- > disk file changes, i.e. it's strictly a binary mode scenario. As > someone has already suggested, if you want to learn more about > Postgres and Python, look at Skytools. I'm not just saying to use > it, read the code and, if you like, offer help with patches. > > Erik Jones So the fun ends in 3, 2, 1 Well, so it looks like the trigger approach is now my path to follow. Yeah, im looking for skytools code, looks very nice, maybe a little outdate (just a quick look, it uses old-style python classes). BTW, the information about WAL system you gave me, just saves me a lot of time. Thanks you for that! Gerardo ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] specifying wal file size
Hi dudes. Im trying to find out how to deal with this. I just receive a 1.2 MB wal file (may some 'windows home version' do that?), and obviosly, when i try to recovery from there, get the 'incorrect file size' error. Acording to the docs, wal file size can be changed at compile time. Not so bad, i can do that. But where is that code? BTW: 1163398 bytes seems like a wrong size for any wal file, isnt? The .backup file may have been created under windows, and im working on Linux. Could that be a problem? Thanks! Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] trying to repair a bad header block
Hi all. I've seen this searching in google. After a select on a table, i got this: ERROR: invalid page header in block 399 of relation "xxx" I read about a tool called pg_filedump, and after some searchs about its usage, i execute pg_filedump $PG_DATA/base/xx/1234 (1234 is the oid of table xxx) As expected, i found the "Invalid header information" in block 399. Lots of this things inside: Block 399 - Block Offset: 0x0031e000 Offsets: Lower1663 (0x067f) Block: Size0 Version 95Upper 0 (0x) LSN: logid 5714531 recoff 0x00e0 Special 60660 (0xecf4) Items: 410 Free Space: 4294965633 Length (including item array): 1660 Error: Invalid header information. -- Item 1 -- Length:0 Offset: 2600 (0x0a28) Flags: 0x00 Item 2 -- Length:0 Offset:0 (0x) Flags: 0x00 Item 3 -- Length:0 Offset:0 (0x) Flags: 0x00 Item 4 -- Length:0 Offset:0 (0x) Flags: 0x00 Item 5 -- Length: 32767 Offset: 32767 (0x7fff) Flags: USED Error: Item contents extend beyond block. BlockSize<8192> Bytes Read<8192> Item Start<65534>. This for several items. Im triyng to 'repair' those items in any way, so i can dump the database and analyze the hardware. There is a way to 'correct' or blank the values somehow? I guess im going to lose some data, iisnt... Any hints? Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] trying to repair a bad header block
> On Wed, Oct 29, 2008 at 6:36 PM, Tom Lane <[EMAIL PROTECTED]> wrote: >> "Scott Marlowe" <[EMAIL PROTECTED]> writes: >>> On Wed, Oct 29, 2008 at 4:23 PM, Tom Lane <[EMAIL PROTECTED]> wrote: If you can tolerate losing the data on that page, just zero out the entire 8K page. dd from /dev/zero is the usual tool. >> >>> Would zero_damaged_pages work here? I know it's a shotgun to kill a >>> flea, but it's also easier and safer for a lot of folks than dding a >>> page in their table. >> >> It would work, but if you have any *other* damaged pages you might >> lose more than you were expecting ... > > Agreed. OTOH, on slip of the fingers for a newbie with dd and the > whole table is gone. I guess it's always a trade off. > > Thanks Tom and Scott! I just use dd for simply creating big files (oh, and once to screw up a entire disk :) Im going to man it in order to zero out that page(s). Wish me lucks, dudes. Thanks! Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] trying to repair a bad header block
> On Wed, Oct 29, 2008 at 7:24 PM, Scott Marlowe <[EMAIL PROTECTED]> > wrote: > > Oh, and to reply to myself and the original poster, you need to figure > out what's causing the pages to get damaged. IT's usually bad > hardware, then a buggy driver, then a buggy kernel / OS that can cause > it. Run lots of tests. > Oh yes, im facing hw problems. All im triyng to do now is 'rebuild' the header blocks in order to do some pg_dump (whichs is failing, off course), set a new machine and get the actual one to the pits :) Thanks again! Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dynamic OUT parameters?
> Gerardo Herzig wrote: > >> 1) There is a way to make a function returning "any amount of any type >> of arguments"? > > RETURNS SETOF RECORD > > The application must, however, know what columns will be output by the > function ahead of time and call it using an explicit column declaration > list. For example, the following function returns a table of width > `_ncols' columns repeated over `_ncols' records: > > CREATE OR REPLACE FUNCTION dyncol(_ncols INTEGER) RETURNS SETOF RECORD > AS > $$ > DECLARE > _out RECORD; > _stm text; > _i integer; > BEGIN > _stm = 'SELECT 1'; > FOR _i IN 2.._ncols LOOP > _stm = _stm || ', ' || _i; > END LOOP; > _stm = _stm || ' FROM generate_series(1,' || _ncols || ');' ; > FOR _out IN EXECUTE _stm LOOP > RETURN NEXT _out; > END LOOP; > END; > $$ LANGUAGE 'plpgsql'; > > > Because Pg must know what the return columns will be before the function > is called, you can't just call it as `dyncol(4)' : > > test=> SELECT dyncol(4); > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "dyncol" line 12 at RETURN NEXT > > you must instead specify a table alias with a column definition, eg: > > test=> SELECT * FROM dyncol(4) AS x(a INTEGER, b INTEGER, c INTEGER, d > INTEGER); > a | b | c | d > ---+---+---+--- > 1 | 2 | 3 | 4 > 1 | 2 | 3 | 4 > 1 | 2 | 3 | 4 > 1 | 2 | 3 | 4 > (4 rows) > > > Of course, nothing stops you from writing another function that provides > this information to the application, so it can call the first function > to get the information required to correctly call your dynamic reporting > function. > >> 2) Can i make a special type "on_the_fly" and returning setof >> "that_type"? > > You're better off using SETOF RECORD, at least in my opinion. > > -- > Craig Ringer Oh, that looks promising. I wrongly supposed that RETURNING SETOF RECORD forces the use of OUT parameters. I will give your idea a try. Thanks Craig! Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dynamic OUT parameters?
> [email protected] wrote: > >> Oh, that looks promising. I wrongly supposed that RETURNING SETOF RECORD >> forces the use of OUT parameters. I will give your idea a try. > > Tom Lane's point about using a refcursor is (unsurprisingly) a good one. > If you return a refcursor from your function, you don't have to do any > special work to call the function, and you can (with most DB access > APIs) FETCH records from the cursor rather conveniently. > > See: > > http://www.postgresql.org/docs/8.3/static/plpgsql-cursors.html > > -- > Craig Ringer > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql Well, aparenty my problem remains, because the boss want that the programmers just need to call select * from report('table_name'). Following your previous sample function, im forced to use it in the form select * from report('table_name') as x(a int, b varchar, c int), and that "as x(...)" is the kind of thing hes triyng to avoid. Same feeling about fetching records at application level. To bad for me, im affraid :( Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
