I have the following function defined:
create function _date_week(int4,int4,int4) returns text language pltcl
immutable as $$
return [clock format [clock scan "$2/$3/$1"] -format "%U"]
$$;
It worked fine in 8.3 but in 8.4 now I try to build an index using the
function and get:
SQL ERROR
Several years ago someone posted a question about how to achieve a
running total of columns in sql. I have been trying to find a solution
to a similar problem for some time and just came up with something that
works great for me so I thought I'd post it for the benefit of the list.
The proble
Tom Lane wrote:
Kyle Bateman <[EMAIL PROTECTED]> writes:
Is there a way to make the optimizer do this?
Sorry, that's not happening for 8.2. Consider using a union all (not
union) across the subledg_N tables directly and then joining to that.
That boils down to being a p
Kyle Bateman wrote:
Tom Lane wrote:
Before 8.2 the optimizer has no ability to rearrange the order of outer
joins. Do you have time to try your test case against CVS HEAD?
I've done some more refinement on my accounting ledger system that has
clarified some of the problems I was h
I get the following message in my postgres log:
ERROR: 42601: syntax error at or near "%" at character 269
LOCATION: yyerror, scan.l:761
I have a fairly complex schema (lots of database objects) and many users
hitting the db with lots of connections, so I'm not really sure what bit
of code i
Tom Lane wrote:
Kyle Bateman <[EMAIL PROTECTED]> writes:
I'm wondering if this might expose a weakness in the optimizer having to
do with left joins.
Before 8.2 the optimizer has no ability to rearrange the order of outer
joins. Do you have time to try your test case
Tom Lane wrote:
Kyle Bateman <[EMAIL PROTECTED]> writes:
But I'm assuming that using an interval-encoded project tree, I would
have to do something like the following to get a progency group:
select * from ledger l, proj p where p.proj_id = l.proj and p.left >
1234 and
Tom Lane wrote:
Kyle Bateman <[EMAIL PROTECTED]> writes:
But I'm assuming that using an interval-encoded project tree, I would
have to do something like the following to get a progency group:
select * from ledger l, proj p where p.proj_id = l.proj and p.left >
12
Florian Weimer wrote:
* Kyle Bateman:
Any ideas about whether/how this can be done?
If the project tree is fairly consistent, it's convenient to encode it
using intervals instead of parent/child intervals. IIRC, Celko's "SQL
for smarties" explains how to d
Seq Scan on vend_org v (cost=0.00..50.99 rows=1799
width=26)
-> Materialize (cost=3.54..3.55 rows=1 width=4)
-> Seq Scan on acct a (cost=0.00..3.54 rows=1 width=4)
Filter: ((code)::text = 'ap'::text)
-> Hash (cost=38.04..38.04 rows=21 width=4)
-> Index Scan using
lling to help maintain the website and
(currently a sad excuse for) documentation.
There is also a run-time library (wylib) for rapid construction of ERP
applications. We have about 30 applications built in the framework
that run our entire business.
Kyle Bateman
Action Target Inc.
Can anyone point me where to look in the system catalogs (or information
schema) to determine in advance whether a given user (or the current
user) will have select permission to a given table or view?
Kyle
---(end of broadcast)---
TIP 6: explai
Tom Lane wrote:
Kyle Bateman <[EMAIL PROTECTED]> writes:
I have a query:
insert into mtr_reg_v_wt (ropnum, inum, pnum, rquant, value, status,
ddate, fr_proj, to_proj) values (28985,1,1014,1,(select cost from
prd_part where pnum = 1014),'work','2005-Nov-15',50,75
I have a query:
insert into mtr_reg_v_wt (ropnum, inum, pnum, rquant, value, status,
ddate, fr_proj, to_proj) values (28985,1,1014,1,(select cost from
prd_part where pnum = 1014),'work','2005-Nov-15',50,75);
That used to work fine under 7.1.3 but now gives the error:
ERROR: cannot handle un
I'm trying to use information_schema.view_column_usage to determine the
native table from which various view columns descend. This is so my
interface can automatically generate the correct foreign key links from
one view to another.
But in the case where a view references two tables linked by a
Hey, anyone know if it is possible to fire a trigger before a select?
I'm considering creating some tables which contain data summarized from
other tables as kind of a cache mechanism. The hope is I can speed up
some queries that get executed a lot (and are kind of slow) by
maintaining data (
I noticed that it seemed a bit slow to query
information_schema.view_column_usage. As I look at the code in
information_schema.sql, I'm not sure why pg_user is referenced twice
(once without an alias). It looks like we can take out the first
pg_user and remove the DISTINCT keyword and this i
If one has a unique-id generating sequence that sometimes is bound
to
have holes in it (ie: it could happen that a nextval(seq) happens
without a corresponding INSERT in the table), then how could one
efficiently scan for these holes to recycle them in subsequent INSERTs?
I'm just looking for
[EMAIL PROTECTED] (Jan Wieck) writes:
>> If one does:
>>
>> [...]
>> grant select on master to a_user;
>> grant select, insert, update, delete on detail to a_user;
>>
>> then if login as "a_user" and does:
>>
>> insert into detail (id, master_id) values (1, 10);
>>
>> this will result in: "ERROR:
Is it possible to defer the check on a primary key constraint (or a check
constraint, for that matter). Here is an example that shows why it
would be nice to be able to do so. We have a real-life scenario that
is similar (but much more complex).
drop table btab;
create table btab (
base
Kyle Bateman wrote:
Hi Jan:
But when I create it with "create constraint trigger" as shown next,
the trigger doesn't seem to be invoked. It says it is created, but
it allows data to pass that would not be allowed with the "create trigger."
So I'm assuming I&#
Hi Jan:
In response to your suggestion about possibly being able to use
"create constraint trigger," I have tried the following:
I have the following trigger function (probably not all that important
what it does...):
create function prd_parm_tf_iu ()
returns opaque as '
declare
t
How can I create a function that will take in two variables and return
an integer, when one of the variables is the tablename ?!
I have tried :
create function tst_func(text, varchar(16))
as
'BEGIN
result=select max(histor
23 matches
Mail list logo
