[SQL] Timestamp accuracy
Hi,
The user manual said the Timestamp type has a microsecond accuracy, but how can
I display the results of a query with microseconds ?
Without formatting the output has an accuracy of 10^-2 second, and with
formatting it is even worse since the formatting strings do not accept anything
under the second.
SQL example:
create table test_ts (ts timestamp);
insert into test_ts values ('2001-2-2 00:00:00.123456789');
select * from test_ts;
ts
---
2001-02-02 00:00:00.12+01
Florian
Re: [SQL] Suggestion for psql: --file -
On Fri, Feb 02, 2001 at 04:16:05PM +0100, Peter Eisentraut wrote: Albert REINER writes: ... P.S: BTW, the man page (7.0.2) of psql is not very clear: it took me some experimentation to find out that you have to do "\set VARIABLE" interactively or give "--set VARIABLE=" to set a variable that does not take a value. Suggested new wording? What about: -v, --variable, --set variable[=[value]] Performs a variable assignment, like the \set internal command. Note that you must separate name and value, if any, by an equal sign on the command line. To unset a variable, leave off the equal sign and the value. To just set a variable without a value, use the equal sign but leave off the value. These assignments are done during a very early state of startup, so variables reserved for internal purposes might get overwritten later. Albert. -- -- Albert Reiner [EMAIL PROTECTED] Deutsch * English * Esperanto * Latine --
Re: [SQL] Bug with rules in 7.0.3?
Tod McQuillin [EMAIL PROTECTED] writes: How the heck can one insert and update generate three rows? Looks like a rule rewriter bug to me. It seems to be fixed in 7.1; I get regression=# SELECT * FROM orders; order_id | menu_id | price --+-+--- 1 | 2 |-1 (1 row) which is the correct result given that rules are executed before the original query. (Which is why you need a trigger for this...) regards, tom lane
Re: [SQL] Suggestion for psql: --file -
Albert REINER writes: Suggested new wording? What about: Works for me. Thanks. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] PL/pgSQL: possible parsing or documentation bug?
"Albert REINER" [EMAIL PROTECTED] writes:
create Function IdOfPossiblyNewAuthor(text) returns int4 as '
declare
id int4;
begin
select id into id from author where name = $1;
raise notice ''ID found: %'', id;
if id is null then
insert into author (name) values ($1);
select currval(''author_id_seq'') into id;
raise debug ''Author inserted. ID: %'', id;
end if;
return id;
end;
' language 'plpgsql' with (IsCachable);
Logically it is clear which "id" should be parsed as the variable,
which as author.id,
No, it is not so clear. Consider the following:
declare
x int4;
y int4;
begin
x := ...;
select x + f1 into y from tab1 where ...;
The intent here is clearly to find a value tab1.f1 in tab1 and then
add the local variable x to form a value for the local variable y.
In general plpgsql will try to match an unqualified name to a variable
before it will consider whether it might be a field name. If you don't
want that, qualify the field name:
select author.id into id from author where name = $1;
Feel free to submit documentation updates to make this clearer...
regards, tom lane
Re: [SQL] Bug with rules in 7.0.3?
On Sat, 3 Feb 2001, Tom Lane wrote: I get regression=# SELECT * FROM orders; order_id | menu_id | price --+-+--- 1 | 2 |-1 (1 row) which is the correct result given that rules are executed before the original query. (Which is why you need a trigger for this...) OK. I think that Bruce's book is inaccurate then. In section D.19 (p. 299), also reproduced on the web at http://www.postgresql.org/docs/aw_pgsql_book/node490.html, this example is given: All new employees must make 5,000 or less: CREATE RULE example_5 AS ON INSERT TO emp WHERE new.salary 5000 DO UPDATE emp SET salary = 5000 WHERE emp.oid = new.oid; -- Tod McQuillin
[SQL] Fw: C function for use from PLpgSQL trigger
Hello all, I posted this (see below) Friday to the interfaces list with no response. Does anyone know if what I'm trying to do is possible, or should I just write the entire thing in a C function trigger? The latter would be unfortunate because I think it would be nice to be able to extend PLpgSQL using C functions like this. Anyway, any help or advice will be much appreciated! Thanks, Joe Hi, I'm trying to create a C function that I can call from within a PLpgSQL trigger function which will return a list of all the values in the NEW record formatted suitably for an insert statement. I can't come up with a way to do this directly from within PLpgSQL (i.e. iterate through an arbitrary number of NEW.attribute). Can anyone tell me how I can either pass the NEW record to the C function (this produces an error message 'NEW used in a non-rule query') or gain access to the trigger tuple from within my C function? It seems that although PLpgSQL is called as a trigger, the C function is called as a regular function (makes sense) and thus has no access to the trigger tuple (my problem). Any help or guidance is greatly appreciated! Thanks, Joe Conway
