Re: [BUGS] BUG #4916: wish: more statistical functions (median, percentiles etc)
Joshua Tolley wrote: On Sat, Jul 11, 2009 at 02:12:22AM +0100, Richard Neill wrote: Thanks for your reply. Sadly, I haven't the time (or expertise) to write this myself. However, the feature would be really useful to have. I'd certainly be willing to make a £200 payment or donation in return. That's very nice of you to make the offer. Pending someone taking you up on it, you might consider your ability to write the functions in some procedural language. They would probably be easier to write, and you'd only have to make them handle data types you're planning to use them with. For instance, there's an example of PL/Perl versions available embedded in the code here: This stuff is pretty trivial to do with PL/R Joe -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] fix: plpgsql: return query and dropped columns problem
Hello
there is fix for bug Re: [BUGS] BUG #4907: stored procedures and changed tables
regards
Pavel Stehule
2009/7/10 Sergey Burladyan :
> Sergey Burladyan writes:
>
>> Alvaro Herrera writes:
>>
>> > Michael Tenenbaum wrote:
>> >
>> > > If I have a stored procedure that returns a set of records of a table, I
>> > > get
>> > > an error message that the procedure's record is the wrong type after I
>> > > change some columns in the table.
>> > >
>> > > Deleting the procedure then rewriting the procedure does not help. The
>> > > only
>> > > thing that works is deleting both the stored procedure and the table and
>> > > starting over again.
>> >
>> > Does it work if you disconnect and connect again?
>>
>> No, example:
>
> More simple:
>
> PostgreSQL 8.4.0 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
> 4.3.3-13) 4.3.3, 32-bit
>
> create table t (i int);
> alter table t add v text; alter table t drop i;
> create function foo() returns setof t language plpgsql as $$begin return
> query select * from t; end$$;
> select foo();
> ERROR: 42804: structure of query does not match function result type
> ПОДРОБНО: Number of returned columns (1) does not match expected column
> count (2).
> КОНТЕКСТ: PL/pgSQL function "foo" line 1 at RETURN QUERY
> РАСПОЛОЖЕНИЕ: validate_tupdesc_compat, pl_exec.c:5143
>
> So, function with RETURNS SETOF tbl does not work if it created after ALTER
> TABLE
>
> 8.3.7 too:
>
> PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
> 4.3.3-5) 4.3.3
>
> create table t (i int);
> alter table t add v text; alter table t drop i;
> create function foo() returns setof t language plpgsql as $$begin return
> query select * from t; end$$;
> select * from foo();
> ERROR: 42804: structure of query does not match function result type
> КОНТЕКСТ: PL/pgSQL function "foo" line 1 at RETURN QUERY
> РАСПОЛОЖЕНИЕ: exec_stmt_return_query, pl_exec.c:2173
>
>
> --
> Sergey Burladyan
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2009-07-12 17:22:57.268901328 +0200
--- ./src/pl/plpgsql/src/pl_exec.c 2009-07-12 16:57:37.037896969 +0200
***
*** 2284,2289
--- 2284,2294
{
Portal portal;
uint32 processed = 0;
+ int i;
+ bool dropped_columns = false;
+ Datum *dvalues;
+ bool *nulls;
+ int natts;
if (!estate->retisset)
ereport(ERROR,
***
*** 2308,2318
validate_tupdesc_compat(estate->rettupdesc, portal->tupDesc,
"structure of query does not match function result type");
while (true)
{
MemoryContext old_cxt;
- int i;
SPI_cursor_fetch(portal, true, 50);
if (SPI_processed == 0)
--- 2313,2330
validate_tupdesc_compat(estate->rettupdesc, portal->tupDesc,
"structure of query does not match function result type");
+ natts = estate->rettupdesc->natts;
+
+ if (natts > portal->tupDesc->natts)
+ {
+ dropped_columns = true;
+ dvalues = (Datum *) palloc0(natts * sizeof(Datum));
+ nulls = (bool *) palloc(natts * sizeof(bool));
+ }
while (true)
{
MemoryContext old_cxt;
SPI_cursor_fetch(portal, true, 50);
if (SPI_processed == 0)
***
*** 2323,2335
{
HeapTuple tuple = SPI_tuptable->vals[i];
! tuplestore_puttuple(estate->tuple_store, tuple);
processed++;
}
MemoryContextSwitchTo(old_cxt);
SPI_freetuptable(SPI_tuptable);
}
SPI_freetuptable(SPI_tuptable);
SPI_cursor_close(portal);
--- 2335,2374
{
HeapTuple tuple = SPI_tuptable->vals[i];
! if (!dropped_columns)
! tuplestore_puttuple(estate->tuple_store, tuple);
! else
! {
! int anum;
! int j = 0;
! bool isnull;
!
! for (anum = 0; anum < natts; anum++)
! {
! if (estate->rettupdesc->attrs[anum]->attisdropped)
! nulls[anum] = true;
! else
! {
! dvalues[anum] = SPI_getbinval(tuple, portal->tupDesc,
! ++j, &isnull);
! nulls[anum] = isnull;
! }
! }
! tuple = heap_form_tuple(estate->rettupdesc, dvalues, nulls);
! tuplestore_puttuple(estate->tuple_store, tuple);
! }
processed++;
}
MemoryContextSwitchTo(old_cxt);
SPI_freetuptable(SPI_tuptable);
}
+
+ if (dropped_columns)
+ {
+ pfree(dvalues);
+ pfree(nulls);
+ }
SPI_freetuptable(SPI_tuptable);
SPI_cursor_close(portal);
***
*** 5127,5132
--- 5166,5172
validate_tupdesc_compat(TupleDesc expected, TupleDesc returned, const char *msg)
{
int i;
+ int j = 0;
const char *dropped_column_type = gettext_noop("N/A (dropped column)");
if (!expected || !returned)
***
*** 5134,5153
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("%s", _(msg;
- if (expected->natts != returned->natts)
- ereport(ERRO
[BUGS] BUG #4917: NULLs Last as a Global Option
The following bug has been logged online: Bug reference: 4917 Logged by: MIchael Blake Email address: michael.bl...@webdynamic.com.au PostgreSQL version: 8.3 Operating system: Linux [Ubuntu 9.04] Description:NULLs Last as a Global Option Details: Could an option be added to postgresql.conf for default ordering of NULL values for different sort orders? That is, I'd like to be able to force NULLs to be last for date types and number types for descending, but first for ascending - the trick is I'd like this to be the default globally. For example: null_ordering_asc = first null_ordering_desc = last -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4785: Installation fails
En, I have that damm problem sloved. Are you using a Non-English Version of Windows? for example, JPN, CHS? If you install your PSQL in an Asian-language windows, be sure that "local" settings should be setted manualy to a supported language, English-USA is good. this is the errmsg when you're using default local settings: Running the post-installation/upgrade actions: Delete the temporary scripts directory... Write the base directory to the ini file... Write the version number to the ini file... Initialising the database cluster (this may take a few minutes)... Executing cscript Script exit code: 0 Script output: Ensuring we can write to the data directory (using cacls): 数据无效。 The files belonging to this database system will be owned by user "GoldenHawk". This user must also own the server process. The database cluster will be initialized with locale Chinese_People's Republic of China.936. initdb: could not find suitable text search configuration for locale Chinese_People's Republic of China.936 The default text search configuration will be set to "simple". fixing permissions on existing directory D:/PSQLDATA ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 32MB creating configuration files ... ok creating template1 database in D:/PSQLDATA/base/1 ... ok initializing pg_authid ... FATAL: database locale is incompatible with operating system DETAIL: The database was initialized with LC_COLLATE "Chinese_Peoples Republic of China.936", which is not recognized by setlocale(). HINT: Recreate the database with another locale or install the missing locale. child process exited with exit code 1 initdb: removing contents of data directory "D:/PSQLDATA" Granting service account access to the data directory (using cacls): 处理的目录: D:\PSQLDATA initcluster.vbs ran to completion Script stderr: Configuring database server startup... Executing cscript Script exit code: 0 Script output: startupcfg.vbs ran to completion Script stderr: -- View this message in context: http://www.nabble.com/BUG--4785%3A-Installation-fails-tp23323059p24456184.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4917: NULLs Last as a Global Option
"MIchael Blake" writes: > Could an option be added to postgresql.conf for default ordering of NULL > values for different sort orders? It could be, but whether it will be is an entirely different question. I think by and large we've discovered that GUC variables that alter the defined semantics of SQL commands are not such a great idea. In any case, you'd have to wait at least a year to solve your problem that way, so fixing it on the client side is probably a better plan. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
