choiche of function language was: Re: [GENERAL] dynamic procedure call
On Sat, 10 May 2008 07:35:36 +0200 "Pavel Stehule" <[EMAIL PROTECTED]> wrote: > your application different execution paths. Generally I can say, so > plpgsql isn't well language for this games, and better is using > plperl, plpython or other external language. It is clear what is at least one of the advantage of plpython or plperl over plpgsql, but then what are the advantages of plpgsql over the rest of the crowd other than resembling the language used in Oracle? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Returning RECORD from PGSQL without custom type?
Instead of doing this: CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint) RETURNS SETOF record AS $body$ ... $body$ LANGUAGE 'plpgsql' VOLATILE; I'd like to be able to do this: CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint) RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS $body$ ... $body$ LANGUAGE 'plpgsql' VOLATILE; Because this is the only function that will be returning that TYPE and I don't want to have to create a separate type definition just for the return results of this function. Maybe even more cool would be if the OUT record was already defined so that I could simply select into that record to send our new rows: RETURN NEXT OUT; OUT.col1name := 12345; RETURN NEXT OUT; SELECT 12345, 'sample' INTO OUT.col1name, OUT.col2name; RETURN NEXT OUT; Just as you've allowed me to define the IN variable names without needing the legacy 'ALIAS $1 ...' format, I'd like to name the returned record column names and types in a simple declaration like I show above. Does this feature request make sense to everyone? It would make programming set returning record functions a lot easier. -- Dante -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extract only numbers from a varchar column
On Tuesday 06 May 2008 07:09:56 Leandro Casadei wrote:
> I need to extract only the first occurence of numbers from a column that
> is varchar.
> abc200xx -> 200
> 340ppsdd -> 340
> 150ytyty555 -> 150
tiger=> select substring('abc200xx300ff','[0-9]+');
substring
---
200
(1 row)
Time: 0.495 ms
tiger=> select substring('340ppsdd','[0-9]+');
substring
---
340
(1 row)
Time: 0.480 ms
tiger=> select substring('150ytyty555','[0-9]+');
substring
---
150
(1 row)
Time: 0.494 ms
tested on 8.2. i don't have 8.3 here, so i don't know if there will
be type coercion issues there. doubt it though.
tiger
--
Gerald Timothy Quimpo [EMAIL PROTECTED]
If you don't know who Knuth is, then you're not a programmer. If
you're a programmer and you don't know who Knuth is, well... you
should probably be fired.
-- scienceblogs.com/goodmath/2008/01/the_genius_of_donald_knuth_typ.php
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: choiche of function language was: Re: [GENERAL] dynamic procedure call
Hello 2008/5/10 Ivan Sergio Borgonovo <[EMAIL PROTECTED]>: > On Sat, 10 May 2008 07:35:36 +0200 > "Pavel Stehule" <[EMAIL PROTECTED]> wrote: > >> your application different execution paths. Generally I can say, so >> plpgsql isn't well language for this games, and better is using >> plperl, plpython or other external language. > > It is clear what is at least one of the advantage of plpython or > plperl over plpgsql, but then what are the advantages of plpgsql over > the rest of the crowd other than resembling the language used in > Oracle? > SQL integration and compatibility with PostgreSQL. PL/pgSQL uses PostgreSQL expression evaluation - so all PostgreSQL functions are simply accessible from plpgsql. Next - plpgsql variables are compatible (are same) with PostgreSQL internal datatypes - so you don't need any conversion between Postgres and plpgsql. plpgsql is best glue of SQL statements. Pavel > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Returning RECORD from PGSQL without custom type?
Hello there exist table returning functions patch, but newer been applied. It's some what you need. Why don't you use OUT variables? try CREATE OR REPLACE FUNCTION fce(IN value int, OUT a int, OUT b int) RETURNS SETOF record AS $$ BEGIN FOR i IN 1..$1 LOOP a := i + 1; b := i + 2; RETURN NEXT; END LOOP; END; $$ LANGUAGE plpgsql IMMUTABLE: SELECT * FROM fce(10); http://www.postgresql.org/docs/8.3/interactive/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS Regards Pavel Stehule 2008/5/10 D. Dante Lorenso <[EMAIL PROTECTED]>: > Instead of doing this: > > CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint) > RETURNS SETOF record AS > $body$ > ... > $body$ > LANGUAGE 'plpgsql' VOLATILE; > > I'd like to be able to do this: > > CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint) > RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS > $body$ > ... > $body$ > LANGUAGE 'plpgsql' VOLATILE; > > Because this is the only function that will be returning that TYPE and I > don't want to have to create a separate type definition just for the return > results of this function. > > Maybe even more cool would be if the OUT record was already defined so that > I could simply select into that record to send our new rows: > > RETURN NEXT OUT; > > OUT.col1name := 12345; > RETURN NEXT OUT; > > SELECT 12345, 'sample' > INTO OUT.col1name, OUT.col2name; > RETURN NEXT OUT; > > Just as you've allowed me to define the IN variable names without needing > the legacy 'ALIAS $1 ...' format, I'd like to name the returned record > column names and types in a simple declaration like I show above. > > Does this feature request make sense to everyone? It would make programming > set returning record functions a lot easier. > > -- Dante > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem returning strings with pgsql 8.3.x
While developing PL/LOLCODE, I've found something wrong with returning
strings from LOLCODE functions using 8.3.0 or greater. Using 8.4beta
from a few days ago, for instance, a function that should return "test
string" returns
"\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F" in
pgsql (sometimes the number of \x7F characters varies). In 8.2.4 it
works fine.
Here's the code involved, from pl_lolcode_call_handler, the call
handler function for PL/LOLCODE. First, the bit that finds the
FmgrInfo structure and typioparam for the result type:
procTup = SearchSysCache(PROCOID,
ObjectIdGetDatum(fcinfo->flinfo->fn_oid), 0, 0, 0);
if (!HeapTupleIsValid(procTup)) elog(ERROR, "Cache lookup
failed for procedure %u", fcinfo->flinfo->fn_oid);
procStruct = (Form_pg_proc) GETSTRUCT(procTup);
typeTup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(procStruct->prorettype), 0, 0, 0);
if (!HeapTupleIsValid(typeTup)) elog(ERROR, "Cache lookup
failed for type %u", procStruct->prorettype);
typeStruct = (Form_pg_type) GETSTRUCT(typeTup);
resultTypeIOParam = getTypeIOParam(typeTup);
fmgr_info_cxt(typeStruct->typinput, &flinfo,
TopMemoryContext); /*CurTransactionContext); */
ReleaseSysCache(typeTup);
Here's the code that converts the return value into a Datum later on
in the function:
if (returnTypeOID != VOIDOID) {
if (returnVal != NULL) {
if (returnVal->type == ident_NOOB)
fcinfo->isnull = true;
else {
SPI_push();
if (returnTypeOID == BOOLOID)
retval =
InputFunctionCall(&flinfo, lolVarGetTroof(returnVal) == lolWIN ?
"TRUE" : "FALSE", resultTypeIOParam, -1);
else {
/* elog(NOTICE,
lolVarGetString(returnVal, true)); */
retval =
InputFunctionCall(&flinfo, lolVarGetString(returnVal, true),
resultTypeIOParam, -1);
}
SPI_pop();
}
}
else {
fcinfo->isnull = true;
}
}
SPI_finish();
/* elog(NOTICE, "PL/LOLCODE ending"); */
return retval;
returnVal is an instance of the struct PL/LOLCODE uses to store its
variables. The key line in this case is the one after the
commented-out call to elog. retval is a Datum type. lolVarGetString()
returns the string value the returnVal struct represents -- I'm
certain of that thanks to gdb and other testing. All other data types
PL/LOLCODE knows about internally seem to return just fine. I'm fairly
certain I'm screwing up memory somewhere, but I can't see what I've
done wrong.
I'm glad to provide further details, but those included above are all
the ones I thought were relevant. Thanks in advance for any help you
can provide.
- Josh / eggyknap
Note: The -hackers list seemed like the place for this post, but its
list description gives instructions to try another list first, hence
the post here.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: choiche of function language was: Re: [GENERAL] dynamic procedure call
On May 10, 2008, at 12:14 AM, Ivan Sergio Borgonovo wrote: On Sat, 10 May 2008 07:35:36 +0200 "Pavel Stehule" <[EMAIL PROTECTED]> wrote: your application different execution paths. Generally I can say, so plpgsql isn't well language for this games, and better is using plperl, plpython or other external language. It is clear what is at least one of the advantage of plpython or plperl over plpgsql, but then what are the advantages of plpgsql over the rest of the crowd other than resembling the language used in Oracle? A much better impedance match to the database. It's designed for doing database-ish things. The biggest advantage there is that your datatypes are the database datatypes and your expression parser is the sql expression parser. That makes using things like timestamp or interval or custom database types simpler and cleaner from pl/pgsql than from, say, pl/perl/ Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: choiche of function language was: Re: [GENERAL] dynamic procedure call
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > It is clear what is at least one of the advantage of plpython or > plperl over plpgsql, but then what are the advantages of plpgsql over > the rest of the crowd other than resembling the language used in > Oracle? Others made some other comments already, but I think the issue that is specifically concerning you is the question of strong vs weak typing. plpgsql is designed as a strongly typed language, meaning that the types of all objects are supposed to be predetermined and not changing. This makes it difficult if not impossible to write stuff that can refer to run-time-selected columns. But you get benefits in terms of better error checking and improved performance --- a weakly typed language could never cache any plans, not even for trivial expressions. We've poked a few loopholes in the strong typing over the years --- the whole business of EXECUTE versus direct evaluation of a query can be seen as allowing weak typing for EXECUTE'd queries. But it's still the language's design center. I think it'd be possible to build a weakly typed language that was just as well integrated with SQL as plpgsql is, but it would likely be markedly slower in use. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: choiche of function language was: Re: [GENERAL] dynamic procedure call
I wrote: > We've poked a few loopholes in the strong typing over the years > --- the whole business of EXECUTE versus direct evaluation of a > query can be seen as allowing weak typing for EXECUTE'd queries. > But it's still the language's design center. Rereading that, it suddenly struck me that Pavel's recent addition of USING to EXECUTE provides a klugy way to get at a run-time-determined member of a row variable, which seems to be the single most-requested facility in this area. I put together the following test case, which tries to print out the values of fields selected by trigger arguments: create or replace function foo() returns trigger as $$ declare r record; begin for i in 1 .. tg_argv[0] loop execute 'select $1 . ' || tg_argv[i] || ' as x' into r using NEW; raise notice '% = %', tg_argv[i], r.x; end loop; return new; end $$ language plpgsql; create table tab(f1 int, f2 text, f3 timestamptz); create trigger footrig before insert on tab for each row execute procedure foo (3,f1,f2,f3); insert into tab values(42, 'foo', now()); (BTW, in this example it's truly annoying that TG_ARGV[] isn't a "real" array that you can use array_lower/array_upper on. Maybe that is worth fixing sometime.) Unfortunately this doesn't quite work, because plpgsql is resolutely strongly typed: NOTICE: f1 = 42 ERROR: type of "r.x" does not match that when preparing the plan CONTEXT: PL/pgSQL function "foo" line 6 at RAISE IOW, it gets through the first cycle of the loop okay, but in the second one the "r.x" subexpression has already been planned on the expectation that r.x is of type int. You can get around this if you are willing to coerce all possible results to the same type, eg text: create or replace function foo() returns trigger as $$ declare t text; begin for i in 1 .. tg_argv[0] loop execute 'select cast ( $1 . ' || tg_argv[i] || ' as text)' into t using new; raise notice '% = %', tg_argv[i], t; end loop; return new; end $$ language plpgsql; et voila: NOTICE: f1 = 42 NOTICE: f2 = foo NOTICE: f3 = 2008-05-10 11:38:33.677035-04 So, it's a hack, and it relies on a feature that won't be out till 8.4, but it *is* possible ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Returning RECORD from PGSQL without custom type?
On Sat, 10 May 2008 02:36:50 -0500 "D. Dante Lorenso" <[EMAIL PROTECTED]> wrote: > Instead of doing this: > >CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint) >RETURNS SETOF record AS >$body$ >... >$body$ >LANGUAGE 'plpgsql' VOLATILE; What's the problem with the above? You don't like to specify the returned type in each "caller"? then CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint out ret1 int, out ret2 text, out ret3 float ) RETURNS SETOF record AS $body$ declare row record; begin for ... ret1:=row.col1; ret2:=row.col2; if(row.col3)<7 then ret3:=row.col3; else ret3:=0; end if; ... $body$ LANGUAGE 'plpgsql' VOLATILE; then you can call select ret2 from my_custom_func(100) where ret1<12; > I'd like to be able to do this: > >CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint) >RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS >$body$ >... >$body$ >LANGUAGE 'plpgsql' VOLATILE; > it looks similar to the above... > RETURN NEXT OUT; > > OUT.col1name := 12345; > RETURN NEXT OUT; > > SELECT 12345, 'sample' > INTO OUT.col1name, OUT.col2name; > RETURN NEXT OUT; I'm not sure if you can... > Does this feature request make sense to everyone? It would make > programming set returning record functions a lot easier. yeah it could be a nice shortcut to define types "locally". Once you call "OUT" the type, you could avoid the ret1:=row. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using Epoch to save timestamps in 4 bytes?
Bruce Momjian writes: I am looking at tens of millions of rows, which is why my predecessor may have used integer to store epoch to save space. Our timestamp has a much larger range than a 4-byte time_t, docs say: 4713 BC 294276 AD Given that all of our dates will fall within what we can store in 4bytes, what would be the easiest way to use epoch as a timestamp? Create a couple of functions so our developers can use a date representation like '20080508 12:40:00' and have the functions tranlate strings to epoch and back? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using Epoch to save timestamps in 4 bytes?
Francisco Reyes <[EMAIL PROTECTED]> writes: > Given that all of our dates will fall within what we can store in 4bytes, > what would be the easiest way to use epoch as a timestamp? Well, if you're bound and determined to create a Y2038 problem for yourself, you could use the legacy "abstime" datatype. Actually, that choice will blow up on you rather sooner than 2038, since I'm sure we'll pull abstime from the system well before it actually starts failing to represent now(). But future-proofing doesn't seem to be part of your design goals, so you might as well use it while it's there. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
