(procedure "__PLTcl_proc_12360682" line 3)
invoked from within
"__PLTcl_proc_12360682 2003 12 20"
in PL/Tcl function "_date_week"
PL/pgSQL function "date_week" line 13 at assignment
Is this a regression or is there a reason the clock command is no longer
a
meric(14,2) as credits
from (select * from ledger order by date) ss;
Enjoy,
Kyle
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
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
at bit
of code is causing the problem. Is there an easy way to get postgres to
spit out the SQL statement it was parsing when it generated the error?
I've experimented with log_error_verbosity and log_min_messages but
still haven't seen any SQL in the log.
Kyle
---
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
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
RROR: cannot handle unplanned sub-select
Anyone know what this means? Is there a good reason why this update
should no longer work? Or is this a bug?
Kyle
wyatterp.com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please
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
needed.
Anyone have any ideas about how I can accomplish this?
Kyle
---(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 th
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
Action Target has a job opening for a programmer proficient in
PostgreSQL and Tcl/Tk. We are located in Provo, UT (home of Novell).
If interested, please contact: [EMAIL PROTECTED]
Thanks!!
---(end of broadcast)---
TIP 6: Have you searched our list
Tom Lane wrote:
Kyle <[EMAIL PROTECTED]> writes:
The problem is, the constraint names ($1,
$2, etc.) are not unique so I don't know how to join the third query
into the fourth.
Hmm, this is messy :-(. The SQL spec requires constraint names to be
uniq
al!)
Should I be looking somehow else in information_schema to see where
foreign keys link? Or am I correct that either:
1. The automatically generated FK constraint_name should be guaranteed
to be unique; or
2. There should be an additional column in the last query somewhere to
tell me what table
onditional code
accordingly.
I want to avoid hardcoding user ID's in the trigger, so I'm trying to
take this approach so it can all be handled simply by granting
privileges on different views to enable/disable the varying levels of
restriction.
Any ideas?
Kyle Bateman
--
What is the SQL command to list users?
CREATE USER, DROP USER, ALTER USER, USER
I just can't seem to find the command to list them.
-Kyle
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
store_vars
return [set store_vars($1) $2]
' LANGUAGE 'pltcl';
-- Fetch a value from a variable.
-- Calling sequence: recall(variable)
create function recall(text) returns int4 as '
global store_vars
return [subst $store_vars($1)]
' LANGUAGE 'pltcl';
Peter Eisentraut wrote:
Kyle writes:
> Peter Eisentraut wrote:
>
> > Kyle writes:
> >
> > > Is there a way to get this to work without granting update to
table b?
> >
> > Update to 7.1.]
>
> I'm on 7.1. Should an RI trigger under 7.1 run as t
Peter Eisentraut wrote:
> Kyle writes:
>
> > Is there a way to get this to work without granting update to table b?
>
> Update to 7.1.]
I'm on 7.1. Should an RI trigger under 7.1 run as the DBA or as the current
user?
>
>
> > Tom, I understand someone was wo
that created them rather than the user
who is executing them. This would help greatly in closing some security
holes like this we are dealing with.
Kyle Bateman
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:Pres
Tom Lane wrote:
> Kyle <[EMAIL PROTECTED]> writes:
> > It appears that the first function would get evaluated first under 7.0.3
> > but the last function gets evaluated first under 7.1. Is that accurate?
>
> Actually, I was under the impression that (all else being e
Any reason why I should expect grants and/or revokes to be slower under
7.1RC3?
I have a script that grants all our privileges and it takes about 5 to
10 times longer to run than it did under 7.0.3.
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;
version:2.1
evaluated first under 7.1. Is that accurate?
Is there a way to control which functions are given precidence?
Kyle
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:President
x-mozilla-cpt:;0
fn:Kyle Ba
ion, inventory management, order
entry, contact management, employee records, AP, payroll, purchasing,
shipping, etc.
Given the right conditions, I'd be willing to GPL this too. Anyone
interested in working on it?
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
ad
= h.hinum)
from cinv_hdr h, cinv_items i, ord_hdr o where
o.ordnum = h.ordnum and
h.ordnum = i.ordnum and
h.hinum = i.hinum and
o.proj = 1051 and
h.status = 'open'
;
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;
version:2.1
emai
it return
int4?
create table ints (
inte
int4
);
create function func1() returns int4 as '
select coalesce(sum(inte),0) from ints;
' language 'sql';
create function func2() returns int4 as '
select coalesce(sum(inte)::int4,0) from ints;
' language &
t('day',(m.tdate::datetime-m.mtr_date::datetime)::timespan),0
from mtr_reg m, prd_part p where m.pnum = p.pnum and m.ttype = 'po' and
m.status = 'clsd'
;
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;
version:2.1
27;wm' and ropnum = m1.ropnum) as start,
m1.tdate::datetime as
finish
from mtr m1 where
m1.ttype = 'po' and
m1.pnum = '2000'
) as dates
;
--Here I try a different method
-- select
select
avg(date_part('day',((select min(td
George
>
I don't know much about notify, but I know you can build arbitrary SQL strings in
PL/TCL. Have you
tried that?
I use a combination of plpgsql and pltcl in my implementation because each one has it
strengths/weaknesses.
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
o
and m.rquant >
(select coalesce(sum(l.quant),0) from pak_lnk l
where m.ttype = l.ttype
and m.ropnum = l.ropnum
and m.inum = l.inum
and m.minum = l.minum
and m.bminum = l.bminum);
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
s happen. If you just want to validate or twiddle an individual
> > tuple as it's inserted/updated, a trigger is a good bet.
>
> But the trigger aproach requires access permissions to the
> base table in the first place, and exactly that's what Kyle
> wa
Tom Lane wrote:
Kyle <[EMAIL PROTECTED]> writes:
> If someone happens to know the primary key of a record they should
not be
> able to access, and they try to update it, I would like the backend
to
> ignore the query (or better yet, raise an exception but I haven't
figured
Tom Lane wrote:
> Kyle <[EMAIL PROTECTED]> writes:
> > If someone happens to know the primary key of a record they should not be
> > able to access, and they try to update it, I would like the backend to
> > ignore the query (or better yet, raise an exception but I hav
Jan Wieck wrote:
Tom Lane wrote:
> Kyle <[EMAIL PROTECTED]> writes:
> > ERROR: Cannot update a view without an appropriate rule.
>
> 7.1 insists that you provide an *unconditional* DO INSTEAD rule
> for a view. What do you think was happening on your old database
>
; and seq = 1;
The record it should update does seem to have status = 'appr' and it
updates fine on my 7.0.3 box.
Any ideas?
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
(backend) or in the frontend.
Anyone have any idea why the difference is so dramatic?
Jan:
Is this the difference between old TCL and new TCL (with multi-port
objects)? Or is there something else about the way the backend handles
large chunks of data that would mark the difference?
begin
rigger t where c.oid = t.tgrelid and tgconstrname
= \'$1\'" {
spi_exec "drop trigger
\\"$d(tgname)\\" on $d(relname)"
}
if {$d(tgname) == {}} {return "No constraint
trigger $1 found"}
return "Drop trigger $d(tgname) on $d(relname)
th,hours,
(select coalesce(sum(r.hours),0)
from pay_req r where r.type = 'r' and r.empl_id = v.empl_id and substr(text(r.wdate),6,2)
= v.month) as "type-r"
from pay_req_v v where
v.empl_id = 1000 and
v.month = '01'
;
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
_items set glid = 1000,
istat = 'c' where glid = 1;
--This one doesn't:
update gl_items set
istat = 'c' where glid = 1;
update gl_hdr
set glid = 1000, hstat = 'c' where glid = 1;
end;
select * from gl_hdr h, gl_items i where h.glid = i.g
text,rightarg = text,procedure =
eqnocase, negator = !=*);
create function neqnocase(text,text) returns boolean as '
begin return upper($1) != upper($2); end;' language 'plpgsql' with
(iscachable);
create operator !=* (leftarg = text,rightarg = text,procedure =
neqnocase
lls in TCL functions as
this has caused us some problems in the past.
BTW, any news on this: Is there a plan for a way to allow a FK
reference to a table without giving the user select,update privilege on
the referenced table? This is currently causing us some grief in our
design.
Kyle
e.
I looked at editing pg_relcheck directly. I don't know if that is legal
or not, but the rcbin column worries me as I'm not sure how/when this
gets derived from the rcsrc column. Anyone know if this is possible to
do (even if it requires some poking around)?
begin:vcard
n:Bateman
);
Wouldn't this eliminate the need for rollback-able sequences?
begin:vcard
n:Bateman;Kyle
tel;fax:801-377-8096
tel;work:801-377-8033x101
x-mozilla-html:FALSE
url:www.actiontarget.com
org:Action Target Inc
adr:;;PO Box 636;Provo;UT;84603;US
version:2.1
email;internet:[EMAIL PROTECTED]
title:President
x-mozilla-cpt:;-15520
fn:Kyle Bateman
end:vcard
OID column in
the referenced table (view). If we could name a column "oid" in a view,
we might have a chance with this approach.
How hard would it be to get the "grant references" thing going? Is
there anything I could do to help?
Grant permissions on columns would also solve this
e right order. But if one of the updates tries to rename to
an already existing record, it fails.
How hard is it to take the deferral mechanism you have for foreign key
references and apply it to the primary key too? It would also be
handy to be able to defer a check constraint.
begin:vcard
n:
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&#
deferral, I can check the consistency
in one direction. But if I try to constrain it in both directions,
I get into a problem where I can't update either table without breaking
the constraints. I'm guessing that if I can get deferral to work,
I should be able to update both tables in a
ion myfunc(text,text) returns result as '
set res [spi_exec "select f1 from $1 where f2
= \'$2\']
return $res
' language 'pltcl';
begin:vcard
n:Bateman;Kyle
tel;fax:801-377-8096
tel;work:801-377-8033x101
x-mozilla-html:FALSE
url:www.actiontarget.c
54 matches
Mail list logo
