Re: [SQL] update from multiple rows
Thanks for your answer (BIn fact the computation is somewhat more complex than an average and the (Bdata set is quite large... I did some test with view & triggers but it's (Btoo slow.. (BMoreover, sometime i need to do big insertion or update and then other time (Bi need juste little update of this table... (BI would like to apply a trigger only for little update but i don't know how (Bto proceed.. Maybe with a condition into the trigger.. But it's adding (Bcomputation time... (B (BThanks again, (BEtienne Adam (B (B (B (B> > I have some trouble updating a table like this one : (B> > date | data_raw | data_sys (B> > 12-01 | 5 | 4.5 (B> > 13-01 | 6 | 6 (B> > 14-01 | 7 | 8 (B> > (B> > I would like to update the 'data_sys' row by computing values of (Bmultiple (B> > 'data_raw' values. I mean for example : (B> > data_sys(13-01) = (data_raw['12-01'] + data_raw['13-01'] + (B> > data_raw['14-01'] )/3; (B> (B>Is there a reason to maintain data_sys in the table? Could you use (B>a view instead? A view could do self joins (join the table against (B>itself) and perform the necessary calculations on the fly when you (B>make a query. That could be expensive if you select large data (B>sets, but for small slices of data it might suffice. (B> (B>Another possibility might be to use a trigger to recalculate data_sys (B>when records are inserted, updated, or deleted. You'd still have (B>to do a potentially expensive one-time update of the entire table, (B>but future updates would then touch only the rows that depend on (B>the data being inserted, updated, or deleted, and the calculated (B>values would always be current. Using a trigger would require some (B>care, however, to avoid cascading updates that are unnecessary or (B>that could result in infinite recursion. (B (B_ $BL5NA%a!<%k$J$i$d$C$Q$j(B $B!V(BMSN Hotmail$B!W(B http://www.hotmail.com/ (B (B (B---(end of broadcast)--- (BTIP 5: Have you checked our extensive FAQ? (B (B http://www.postgresql.org/docs/faq
Re: [SQL] Question about a select
In article <[EMAIL PROTECTED]>, Kretschmer Andreas <[EMAIL PROTECTED]> writes: > Hi, > I have a table with this columns: machine, date and area. > Now i need a view with machine an week, calculated from date, and > sum(area), where date between CURRENT_DATE and now+N days. > Okay, this is not the problem, it works fine. > (the original table and the view is more complex) > The problem is, i need also rows for machine and week with no entrys in > the table. For this rows the sum(area) shold be NULL ore 0. > In other words: for every machine i need M rows, and M must be constant. Week values don't automagically spring into existance when there are no corresponding entries in your table. Use a set-returning function for generating the week values you're interested in, and left-join your table to the SRF. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] returning a record from PL/pgSQL
On Fri, Jan 21, 2005 at 12:41:09PM +0100, KÖPFERL Robert wrote: > > I just tried hard to return > a single record fromout a plpgsql-function. While the (otherwise excelent) > documentation didn't give me an answer, I found out that this works: > > select into ret false, balance, balance; > return ret; > > while ret is a composite type. > > This construction however tastes not good to me. Is there a nicer way? In the "Declarations" section of the PL/pgSQL documentation, under "Row Types," is the following: The individual fields of the row value are accessed using the usual dot notation, for example rowvar.field. and under "RETURN" in the "Control Structures" section is this: To return a composite (row) value, you must write a record or row variable as the expression. So you could to the following: ret.field1 := value1; ret.field2 := value2; ret.field3 := value3; RETURN ret; Internally, however, each expression in the above assignments would be evaluated using a SELECT statement, so whether this code is "nicer" than what you wrote depends on what you mean by "nice." -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Invalid Input syntax for type bigint
hello everyone,
I am trying to run the proc below but get an error : invalid input
syntax for type bigint: "2004-10-26" Can anyone suggest what I am
doing wrong here?
Rx
-- Function: public.getdateallocated(date, date)
DROP FUNCTION public.getdateallocated(date, date);
CREATE OR REPLACE FUNCTION public.getdateallocated(date, date)
RETURNS text AS
'Declare
workflow_t ix_workflow_task%ROWTYPE;
BEGIN
SELECT ix_workflow_task."DATE_COMPLETED",
ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID", ix_workflow_task."TYPE"
INTO workflow_t from ix_workflow_task
INNER JOIN ix_workflow_instance ON
ix_workflow_task."WORKFLOW_INSTANCE_KEY" =
ix_workflow_instance."WORKFLOW_INSTANCE_KEY"
INNER JOIN ix_workflow_instance_to_domain ON
ix_workflow_instance_to_domain."WORKFLOW_INSTANCE_KEY" =
ix_workflow_instance."WORKFLOW_INSTANCE_KEY"
INNER JOIN ix_core_case ON
ix_workflow_instance_to_domain."DOMAIN_KEY" =
ix_core_case."CORECASEKEY"
where to_char(ix_workflow_task."DATE_COMPLETED", \'DD-MM-\') <> \'\'
AND ix_core_case."DELETED" = 0
AND ("CORECASEKEY" in (select * FROM getStatusSwitch($1,$2,
\'Assessment\', \'Prosecution\'))
OR "CORECASEKEY" in (select * from
getStatusSwitch($1,$2, \'Assessment\', \'Investigation\'))
OR "CORECASEKEY" in (select * from
getStatusSwitch($1,$2, \'Assessment\', \'Other\')))
group by ix_workflow_task."DATE_COMPLETED",
ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID", ix_workflow_task."TYPE"
having (lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID") LIKE
\'organise surveillance - 9b\' AND ix_workflow_task."TYPE" = \'Human\'
or lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID")
LIKE \'start case mix workflow - 9\' AND ix_workflow_task."TYPE" =
\'System\'
or lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID") LIKE
\'finalise case - 13\' AND ix_workflow_task."TYPE" = \'Human\'
or lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID") LIKE
\'complete final priority smart form - 39\' AND
ix_workflow_task."TYPE" = \'Human\'
or lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID") LIKE
\'check for case mix type - 17\' AND ix_workflow_task."TYPE" =
\'System\')
and (ix_workflow_task."DATE_COMPLETED" >= $1 and
ix_workflow_task."DATE_COMPLETED" <= $2);
return workflow_t."WORKFLOW_ACTIVITY_XPDL_ID" ||
to_char(workflow_t."DATE_COMPLETED", \'DD-MM-\');
END;
'
LANGUAGE 'plpgsql' VOLATILE;
select getdateallocated('10/10/04','12/12/04');
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] OID's
Hello, pgsql-sql and Michael.
MF> On Fri, Jan 21, 2005 at 11:17:34AM +0500, Mihail Nasedkin wrote:
>> I have already read about "System Columns" of the PostgreSQL documentation.
>> In the table "pg_catalog.pg_attribute" column "attrelid" contain
>> only "system OID's" but not OID's from records of the user tables.
>>
>> But I would like to use OID's of all records of the all my tables.
>> ^^^^^^
MF> If you want to do that then you'll need to know which tables have
MF> OIDs. If you just want rows then you could query pg_class and
MF> filter on the relhasoids column, but if you also want things like
MF> large objects then you might need to query pg_attribute and look
MF> for all columns having an "oid" type. It sounds like you're not
MF> interested in the latter, however.
I don't need to know which tables have OIDS. I want know which system
table contain column OID with all OID's inserted into my tables. Or is
there system function that return last insert oid like
$sth->{'pg_oid_status'} in the DBD::Pg?
>> I try to use rules on INSERT action of my tables to store last insert
>> oid, but at the moment of the INSERT row into table OID value
>> inaccessible (unknown).
MF> A row's OID should be visible in an AFTER trigger.
OK.
>> >> I would like use some SQL queries with the all OID's.
>>
>> MF> To what end? Are you aware that PostgreSQL allows tables to be
>> MF> created without OIDs?
>>
>> Yes, of course, but in my case I create tables with OID and then want use
>> OID of all records of the all tables as one column in some query.
MF> Are you aware that OIDs aren't guaranteed to be unique due to
MF> wraparound? If you have a UNIQUE constraint on each table's oid
MF> column then the combination of (tableoid, oid) might serve your
MF> needs.
I think that OIDs are guaranteed to be unique according to FAQ 4.16) What is an
OID? What is a TID?
>> I think what system of OID's is very useful for application!
MF> Assigning row IDs from a common sequence could serve the same
MF> purpose, and since sequences are 64 bits you wouldn't be as subject
MF> to a wraparound problem (OIDs are 32 bits).
OIDs are stored as 4-byte integers (see FAQ)
>> MF> What problem are you trying to solve?
>>
>> For example, I want to fetching all rows of the several tables in one
>> query by means of LEFT JOIN, but not use UNION operator.
MF> Again, what problem are you trying to solve? Using OIDs might not
MF> be the best solution, and if we knew what you're trying to do then
MF> we might be able to suggest alternatives.
Why alternatives if already exists system of the identification of all
rows from all tables.
--
Regards,
Mihail Nasedkin mailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Invalid Input syntax for type bigint
On Sun, Jan 23, 2005 at 02:59:36PM -0800, Ryan Miranda wrote: > I am trying to run the proc below but get an error : invalid input > syntax for type bigint: "2004-10-26" Can anyone suggest what I am > doing wrong here? Apparently you're trying to use a date where a bigint is expected. One possibility might be here: > SELECT ix_workflow_task."DATE_COMPLETED", > ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID", ix_workflow_task."TYPE" > INTO workflow_t from ix_workflow_task You declared workflow_t to be ix_workflow_task%ROWTYPE but you're only selecting certain fields into it. Is the first field in ix_workflow_task perchance a bigint? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] OID's
On Mon, Jan 24, 2005 at 08:57:43AM +0500, Mihail Nasedkin wrote:
> I don't need to know which tables have OIDS. I want know which system
> table contain column OID with all OID's inserted into my tables.
No system table contains a list of all OIDs, if that's what you're
asking.
> Or is there system function that return last insert oid like
> $sth->{'pg_oid_status'} in the DBD::Pg?
In a PL/pgSQL function you can use GET DIAGNOSTICS to get RESULT_OID
after an INSERT; in an AFTER trigger you can refer to a row's oid
column; in client code that uses libpq you can call PQoidValue().
If you're using another interface then see its documentation. I'm
not aware of a function that you can call directly from SQL.
> MF> Are you aware that OIDs aren't guaranteed to be unique due to
> MF> wraparound? If you have a UNIQUE constraint on each table's oid
> MF> column then the combination of (tableoid, oid) might serve your
> MF> needs.
>
> I think that OIDs are guaranteed to be unique according to FAQ 4.16)
> What is an OID? What is a TID?
Actually it's FAQ 4.15:
http://www.postgresql.org/files/documentation/faqs/FAQ.html#4.15
The wording is misleading when it says that OIDs are unique; it
should probably be corrected, although it does mention that OIDs
can overflow. For more information see "Object Identifier Types"
in the "Data Types" chapter of the documentation:
http://www.postgresql.org/docs/8.0/static/datatype-oid.html
"The oid type is currently implemented as an unsigned four-byte
integer. Therefore, it is not large enough to provide database-wide
uniqueness in large databases, or even in large individual tables.
So, using a user-created table's OID column as a primary key is
discouraged. OIDs are best used only for references to system
tables."
See also "System Columns" in the "Data Definition" chapter:
http://www.postgresql.org/docs/8.0/static/ddl-system-columns.html
"OIDs are 32-bit quantities and are assigned from a single cluster-wide
counter. In a large or long-lived database, it is possible for the
counter to wrap around. Hence, it is bad practice to assume that
OIDs are unique, unless you take steps to ensure that this is the
case."
The documentation gives advice on how to use OIDs as unique identifiers
but recommends using a sequence (serial) instead.
> MF> Assigning row IDs from a common sequence could serve the same
> MF> purpose, and since sequences are 64 bits you wouldn't be as subject
> MF> to a wraparound problem (OIDs are 32 bits).
>
> OIDs are stored as 4-byte integers (see FAQ)
That's what I said. 4 bytes = 32 bits, assuming the 8-bit bytes
that are nearly universal. (Would PostgreSQL even run on systems
with, say, 9-bit bytes?)
> MF> Again, what problem are you trying to solve? Using OIDs might not
> MF> be the best solution, and if we knew what you're trying to do then
> MF> we might be able to suggest alternatives.
>
> Why alternatives if already exists system of the identification of all
> rows from all tables.
Because that system doesn't guarantee uniqueness, at least not
without special care. You might be able to use the combination of
(tableoid, oid) as a unique row identifier if each table has a
unique constraint on its oid column, but you'll need to handle cases
where the oid has wrapped around and the constraint is violated
when you insert a new row. If your database isn't heavily used
then the chance of that happening might be unlikely, but it's
precisely the unlikely that can cause strange, hard-to-debug problems
because you weren't expecting it and the circumstances are difficult
to duplicate.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] OID's
Thanks Michael for answer January, 24 2005, 9:58:35:
MF> On Mon, Jan 24, 2005 at 08:57:43AM +0500, Mihail Nasedkin wrote:
>> I don't need to know which tables have OIDS. I want know which system
>> table contain column OID with all OID's inserted into my tables.
MF> No system table contains a list of all OIDs, if that's what you're
MF> asking.
Yes, ok.
>> Or is there system function that return last insert oid like
>> $sth->{'pg_oid_status'} in the DBD::Pg?
MF> In a PL/pgSQL function you can use GET DIAGNOSTICS to get RESULT_OID
MF> after an INSERT; in an AFTER trigger you can refer to a row's oid
MF> column; in client code that uses libpq you can call PQoidValue().
MF> If you're using another interface then see its documentation. I'm
MF> not aware of a function that you can call directly from SQL.
Ok.
>> MF> Are you aware that OIDs aren't guaranteed to be unique due to
>> MF> wraparound? If you have a UNIQUE constraint on each table's oid
>> MF> column then the combination of (tableoid, oid) might serve your
>> MF> needs.
>>
>> I think that OIDs are guaranteed to be unique according to FAQ 4.16)
>> What is an OID? What is a TID?
MF> Actually it's FAQ 4.15:
MF> http://www.postgresql.org/files/documentation/faqs/FAQ.html#4.15
MF> The wording is misleading when it says that OIDs are unique; it
MF> should probably be corrected, although it does mention that OIDs
MF> can overflow. For more information see "Object Identifier Types"
MF> in the "Data Types" chapter of the documentation:
MF> http://www.postgresql.org/docs/8.0/static/datatype-oid.html
MF> "The oid type is currently implemented as an unsigned four-byte
MF> integer. Therefore, it is not large enough to provide database-wide
MF> uniqueness in large databases, or even in large individual tables.
MF> So, using a user-created table's OID column as a primary key is
MF> discouraged. OIDs are best used only for references to system
MF> tables."
MF> See also "System Columns" in the "Data Definition" chapter:
MF> http://www.postgresql.org/docs/8.0/static/ddl-system-columns.html
MF> "OIDs are 32-bit quantities and are assigned from a single cluster-wide
MF> counter. In a large or long-lived database, it is possible for the
MF> counter to wrap around. Hence, it is bad practice to assume that
MF> OIDs are unique, unless you take steps to ensure that this is the
MF> case."
MF> The documentation gives advice on how to use OIDs as unique identifiers
MF> but recommends using a sequence (serial) instead.
Persuasively, ok.
>> MF> Assigning row IDs from a common sequence could serve the same
>> MF> purpose, and since sequences are 64 bits you wouldn't be as subject
>> MF> to a wraparound problem (OIDs are 32 bits).
>>
>> OIDs are stored as 4-byte integers (see FAQ)
MF> That's what I said. 4 bytes = 32 bits, assuming the 8-bit bytes
MF> that are nearly universal. (Would PostgreSQL even run on systems
MF> with, say, 9-bit bytes?)
My mistake, ok.
>> MF> Again, what problem are you trying to solve? Using OIDs might not
>> MF> be the best solution, and if we knew what you're trying to do then
>> MF> we might be able to suggest alternatives.
>>
>> Why alternatives if already exists system of the identification of all
>> rows from all tables.
MF> Because that system doesn't guarantee uniqueness, at least not
MF> without special care. You might be able to use the combination of
MF> (tableoid, oid) as a unique row identifier if each table has a
MF> unique constraint on its oid column, but you'll need to handle cases
MF> where the oid has wrapped around and the constraint is violated
MF> when you insert a new row. If your database isn't heavily used
MF> then the chance of that happening might be unlikely, but it's
MF> precisely the unlikely that can cause strange, hard-to-debug problems
MF> because you weren't expecting it and the circumstances are difficult
MF> to duplicate.
Ok.
I think, that we close the theme of the OIDs for next time.
--
Mihail Nasedkin mailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] update from multiple rows
On Sun, Jan 23, 2005 at 11:36:11AM +, adam etienne wrote: > In fact the computation is somewhat more complex than an average and the > data set is quite large... I did some test with view & triggers but it's > too slow.. Can you provide any more detail about the algorithm and the number of rows that you might have to insert or update? How did the test triggers work? If you used row-level triggers and if a trigger on one row updated multiple rows, then you might have been updating rows more times than necessary (once by explicit update and one or more unnecessary times by triggers on other rows). -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
