[SQL] RE: Create table in functions
On Thu, 24 Aug 2000, Hiroshi Inoue wrote: > Hmm,Andreas's original function seems to contain other statements. > If the function contains DML statements for the table Temp_Num_Table, > it wouldn't work properly. i.e 1st call would work but 2nd call woudn't. That's the problem. I'm in the process of porting a set of about 30 Stroed Procedures from MS-SQL server to PostgreSQL and have just managed only 3 :-( because of some "This is not possible with PostgreSQL". To make clear what I want to do, I just copy the original stored procedure, which might be not so clever so that it would be possible to go without the table creation: CREATE Procedure TilleA.pHelpCountNames ( @Arbeitsgruppeshort varchar(255) , @Condition varchar(255) ) /* Count names in table Mitarbeiter which belong to the group Arbeitsgruppeshort and match the condition @Condition */ As Declare @Query varchar(1024) Declare @num int Select @num = 0 if @ArbeitsgruppeShort is NULL or @ArbeitsGruppeShort = '' begin Select @ArbeitsgruppeShort = '' end else begin Select @ArbeitsgruppeShort = ' AND a.ArbeitsgruppeShort = ' + + @ArbeitsgruppeShort + end Create Table #NumTable ( Num integer ) Select @Query = 'Insert Into #NumTable (Num) ' + 'SELECT Count (*) ' + 'FROM Mitarbeiter m ' + 'INNER JOIN tm2nMitarbeiterArbeitsgruppe t ON m.IdMitarbeiter = t.IdMitarbeiter ' + 'INNER JOIN Arbeitsgruppe aON t.IdArbeitsgruppe = a.IdArbeitsgruppe ' + 'WHERE ' + @Condition + @ArbeitsgruppeShort Exec (@Query) Select @num=(Select Max(Num) From #NumTable) Drop Table #NumTable return @num May be there are some clever ways to avoid the temporary table. I really wonder if my solution is in fact very clever because I'm unsure if it is thread-safe. Any hints? Kind regards Andreas.
[SQL] Re: Date of creation and of change
On Wed, 23 Aug 2000, hlefebvre wrote:
> Yes. The keywords NEW / OLD are available only in triggers
> see
> http://www.postgresql.org/users-lounge/docs/7.0/user/c40874113.htm#AEN4286
Well, I believe that, but
CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS '
BEGIN
ChangedAt := timestamp(''now'');
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TABLE WebSeite (
IdWebSeite int4 DEFAULT nextval('seqwebseite'::text) NOT NULL,
CreatedAt timestamp DEFAULT now(),
changedat timestamp DEFAULT now(),
...
);
CREATE TABLE Menu (
IdMenu int4 DEFAULT nextval('seqmenu'::text) NOT NULL,
CreatedAt timestamp DEFAULT now(),
ChangedAt timestamp DEFAULT now(),
...
);
CREATE TABLE MenuItem (
IdMenu int4 DEFAULT nextval('seqmenu'::text) NOT NULL,
CreatedAt timestamp DEFAULT now(),
ChangedAt timestamp DEFAULT now(),
...
);
CREATE TRIGGER webseite_changed_at_timestamp BEFORE INSERT OR UPDATE ON WebSeite
FOR EACH ROW EXECUTE PROCEDURE changed_at_timestamp();
CREATE TRIGGER menu_changed_at_timestamp BEFORE INSERT OR UPDATE ON Menu
FOR EACH ROW EXECUTE PROCEDURE changed_at_timestamp();
CREATE TRIGGER menuitem_changed_at_timestamp BEFORE INSERT OR UPDATE ON MenuItem
FOR EACH ROW EXECUTE PROCEDURE changed_at_timestamp();
web=# insert into menu (IdMenu, ...) values (3, ... );
ERROR: parser: parse error at or near "changedat"
What's the problem here. Is there a conflict between the definition with
DEFAULT now() and the TRIGGER BEFORE INSERT OR UPDATE. Should perhaps
be the DEFAULT in the definition be removed or just the INSERT in
the TRIGGER? Or is there a completely different problem?
Kind regards
Andreas.
Re: [SQL] Re: Date of creation and of change
Andreas Tille wrote:
>
> On Wed, 23 Aug 2000, hlefebvre wrote:
>
> > Yes. The keywords NEW / OLD are available only in triggers
> > see
> > http://www.postgresql.org/users-lounge/docs/7.0/user/c40874113.htm#AEN4286
> Well, I believe that, but
>
> CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS '
> BEGIN
> ChangedAt := timestamp(''now'');
> RETURN NEW;
> END;
> ' LANGUAGE 'plpgsql';
> web=# insert into menu (IdMenu, ...) values (3, ... );
> ERROR: parser: parse error at or near "changedat"
>
> What's the problem here.
No I suppose that the problem is the identifier "changedat" is unknown.
You must probably prefix it : NEW.changedat
CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS '
BEGIN
NEW.ChangedAt := timestamp(''now'');
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
I didn't test it, but as pgSQL looks like Oracle, it should be ok :-)
regards
[SQL] Re: Date of creation and of change
On Fri, 25 Aug 2000, hlefebvre wrote:
> No I suppose that the problem is the identifier "changedat" is unknown.
>
> You must probably prefix it : NEW.changedat
>
> CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS '
> BEGIN
> NEW.ChangedAt := timestamp(''now'');
> RETURN NEW;
> END;
> ' LANGUAGE 'plpgsql';
>
> I didn't test it, but as pgSQL looks like Oracle, it should be ok :-)
This avoids the error message, but doesn't have any effect to the value
of ChangedAt. It just remains the same as CreatedAt :-(.
Kind regards
Andreas.
Re: [SQL] Re: Date of creation and of change
Andreas Tille wrote:
>
> On Fri, 25 Aug 2000, hlefebvre wrote:
>
> > No I suppose that the problem is the identifier "changedat" is unknown.
> >
> > You must probably prefix it : NEW.changedat
> >
> > CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS '
> > BEGIN
> > NEW.ChangedAt := timestamp(''now'');
> > RETURN NEW;
> > END;
> > ' LANGUAGE 'plpgsql';
> >
> > I didn't test it, but as pgSQL looks like Oracle, it should be ok :-)
> This avoids the error message, but doesn't have any effect to the value
> of ChangedAt. It just remains the same as CreatedAt :-(.
>
I tried...
I fact it seems that it doesn'nt work if you don't use the changedat
column in your UPDATE our insert statement. Just put null, and it will
be ok I think.
Re: [SQL] Re: Date of creation and of change
Andreas Tille <[EMAIL PROTECTED]> writes:
>> NEW.ChangedAt := timestamp(''now'');
> This avoids the error message, but doesn't have any effect to the value
> of ChangedAt. It just remains the same as CreatedAt :-(.
I think you are getting burnt by premature constant folding --- see
nearby discussion of how to define a column default that gives the
time of insertion. You need to write this as
NEW.ChangedAt := now();
to prevent the system from reducing timestamp('now') to a constant
when the function is first executed.
regards, tom lane
[SQL] Re: Date of creation and of change
On Fri, 25 Aug 2000, Tom Lane wrote:
> I think you are getting burnt by premature constant folding --- see
> nearby discussion of how to define a column default that gives the
> time of insertion. You need to write this as
> NEW.ChangedAt := now();
> to prevent the system from reducing timestamp('now') to a constant
> when the function is first executed.
This doesn't work, too.
It just puts allways the constant time when the function was created
into the database. May be it has to be escaped in somw way??
I don't know how to follow hlefebvre's hint to "put null" into the
field.
Bay the way: If we once solved the problem it might be a topic for
the FAQ, perhaps?
Kind regards
Andreas.
Re: [SQL] Re: Date of creation and of change
Tom Lane wrote:
>
> Andreas Tille <[EMAIL PROTECTED]> writes:
> >> NEW.ChangedAt := timestamp(''now'');
>
> > This avoids the error message, but doesn't have any effect to the value
> > of ChangedAt. It just remains the same as CreatedAt :-(.
>
> I think you are getting burnt by premature constant folding --- see
> nearby discussion of how to define a column default that gives the
> time of insertion. You need to write this as
> NEW.ChangedAt := now();
> to prevent the system from reducing timestamp('now') to a constant
> when the function is first executed.
>
> regards, tom lane
yep you're right :
aegir=#
aegir=# drop table menu
aegir-# ;
DROP
aegir=# drop function changed_at_timestamp() ;
DROP
aegir=# CREATE TABLE Menu (
aegir(# CreatedAt timestamp DEFAULT now(),
aegir(# ChangedAt timestamp
aegir(# );
CREATE
aegir=#
aegir=#
aegir=# CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS '
aegir'# BEGIN
aegir'# NEW.ChangedAt := now();
aegir'# RETURN NEW;
aegir'# END;
aegir'# ' LANGUAGE 'plpgsql';
CREATE
aegir=#
aegir=#
aegir=# CREATE TRIGGER menu_changed_at_timestamp BEFORE INSERT OR
UPDATE ON Men
u
aegir-# FOR EACH ROW EXECUTE PROCEDURE changed_at_timestamp();
CREATE
aegir=#
aegir=# insert into menu(createdat) values(null);
INSERT 27700 1
aegir=# select * from menu;
createdat | changedat
---+
| 2000-08-25 16:29:28+02
(1 row)
aegir=# insert into menu(createdat) values(null);
INSERT 27701 1
aegir=# select * from menu;
createdat | changedat
---+
| 2000-08-25 16:29:28+02
| 2000-08-25 16:30:53+02
(2 rows)
aegir=# update menu set createdat = now();
UPDATE 2
aegir=# select * from menu;
createdat| changedat
+
2000-08-25 16:31:24+02 | 2000-08-25 16:31:24+02
2000-08-25 16:31:24+02 | 2000-08-25 16:31:24+02
(2 rows)
[SQL] Re: Date of creation and of change
On Fri, 25 Aug 2000, hlefebvre wrote:
> Tom Lane wrote:
> > NEW.ChangedAt := now();
> > to prevent the system from reducing timestamp('now') to a constant
> > when the function is first executed.
> >
> > regards, tom lane
> yep you're right :
You both are completely right. Forget about my previous mail. I made
a boring mistake and left the old state untouched. Now it works.
By the way: Hwo can I prevent Access from warning me about the fact,
that "another user" (well it's the postgres server) has changed the
data set while I was edditing it? (In general it's no problem, but
if I try two changes immediately the second change will be started
with this boring warning.) But this is perhaps off topic in this
list
Kind regards and many thanks to you all
Andreas.
Re: [SQL] Re: Date of creation and of change
Andreas Tille wrote:
>
> On Fri, 25 Aug 2000, Tom Lane wrote:
>
> > I think you are getting burnt by premature constant folding --- see
> > nearby discussion of how to define a column default that gives the
> > time of insertion. You need to write this as
> > NEW.ChangedAt := now();
> > to prevent the system from reducing timestamp('now') to a constant
> > when the function is first executed.
> This doesn't work, too.
> It just puts allways the constant time when the function was created
> into the database. May be it has to be escaped in somw way??
>
> I don't know how to follow hlefebvre's hint to "put null" into the
> field.
As I said, I was wrong, and Tom is right.
I was suspecting a problem if the field is not used in the query, but
the pb was about the now() function.
> Bay the way: If we once solved the problem it might be a topic for
> the FAQ, perhaps?
IMHO this is a documentation problem.
There is no example about such trigger who will modify data, and 'now'
is described as "transaction time".
also, in date/time function now() is not mentionned
(doc about pgSQL 7.0 on the web site at
http://www.postgresql.org/users-lounge/docs/7.0/user/functions2872.htm)
Re: [SQL] weird structure
> > Consider the following tables/fields: > > table "person": fields "p_id", "p_name". > > table "person_attribute": fields "a_id", "a_name". > > table "person_data": fields "d_person_id", "d_attribute_id", > > "d_value". > > > > Also consider that a person may not have data related to all possible > > attributes. > > > > Using this structure, how could I retrieve in one row with a single > > select statement all attributes from a person (showing null to > > attributes that were not registered to him)? > > > > Thanks for any suggestion, > > -- > > Renato > > Sao Paulo - SP - Brasil > > [EMAIL PROTECTED] > > I did similar things using the following technique: > in frontend I gather all attributes from person_attribute and then > generate a query like this: > > select *, (select distinct d_value from person_data where d_person_id = > person.p_id and > d_attribute_id = 'here first attribute id'), (select distinct d_value > from person_data where d_person_id = person.p_id and d_attribute_id = > 'here second attribute id'), ... from person > -- > Sincerely yours, > Yury Yury... That worked perfectly! Thank you very much! Tell me, I've never seen this sql construction before, is it specific to postgres? Or should it also work on other databases? And is there any relevant performance issue considering this kind of database schema and its peculiar sql commands? Thanks again! -- Renato Sao Paulo - SP - Brasil [EMAIL PROTECTED]
[SQL] queries and inserts
Hi, I am interested in how to speed up storage. About 1000 or more inserts may need to be performed at a time , and before each insert I need to look up its key from the reference table. So each insert is actually a query followed by an insert. The tables concerned are : CREATE TABLE referencetable(idx serial, rcol1 int4 NOT NULL, rcol2 int4 NOT NULL, rcol3 varchar(20) NOT NULL, rcol4 varchar(20), PRIMARY KEY(idx) ... CREATE INDEX index_referencetable on referencetable(rcol1, rcol2, rcol3, rcol4); CREATE TABLE datatable ( ref_idx int4, start_date_offset int4 NOT NULL, stop_date_offset int4 NOT NULL, dcol4 float NOT NULL, dcol5 float NOT NULL, PRIMARY KEY(ref_idx, start_date_offset), CONSTRAINT c1 FOREIGN KEY(ref_idx) REFERENCES referencetable(idx) ); I need to do the following sequence n number of times - 1. select idx (as key) from referencetable where col1=c1 and col2=c2 and col3=c3 and col4=c4; (Would an initial 'select into temptable' help here since for a large number of these queries 'c1' and 'c2' comnbinations would remain constant ?) 2. insert into datatable values(key, ); I am using JDBC interface of postgresql-7.0.2 on Linux. 'referencetable' has about 1000 records, it can keep growing. 'datatable' has about 3 million records, it would grow at a very fast rate. Storing 2000 records takes around 75 seconds after I vacuum analyze. (before that it took around 40 seconds - ???) . I am performing all the inserts ( including the lookup) as one transaction. Thanks, Rini __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
