[SQL] Beginner problems with functions (Was: Is this the wrong list?)
On Wed, 16 Aug 2000, Stephan Szabo wrote on [EMAIL PROTECTED]:
(sorry for the crossposting, just to tell the list that I now switched to
the right one hopefully)
> I think the thing is that most people don't have basic examples, they
Perhaps someone knows one nice doc. I only found some hints for
ma problems in the PGSQL-Part of the Bruce Momjian book. But
may be PGSQL is in fact the thing I want and so I may possibly stick to
that. Now here is the first question about that:
web=# create function atTest ( varchar )
web-# returns bool
web-# As ' BEGIN
web'# Select * From Mitarbeiter Where FName = $1 ;
web'# IF NOT FOUND THEN
web'# RETURN ''f'' ;
web'# ELSE
web'# RETURN ''t'' ;
web'# END IF ;
web'# END; '
web-# language 'plpgsql' ;
CREATE
web=# SELECT attest ( 'Tille' ) ;
ERROR: unexpected SELECT query in exec_stmt_execsql()
web=#
Could somebody enlighten me, what here goes wrong?
> have whatever things they particularly needed. However, there
> are a couple defined in the create_function_2 regression test.
Thanks for your hint. I tried to check these examples, but found that
setof beast is not well documented.
I tested kind of this
> CREATE FUNCTION hobbies(person)
>RETURNS setof hobbies_r
>AS 'select * from hobbies_r where person = $1.name'
>LANGUAGE 'sql';
But it returns just did:
web=# SELECT my_test ( ) ;
?column?
---
136437368
136437368
136437368
...
I had the hope to get the contents of the table like if I would
do 'SELECT * FROM table;'
Also kind of
RETURNS SETOF varchar
AS ' SELECT * FROM table ; '
doesn't do the trick, because this is syntactical wrong.
To explain what I'm intendet to do: I want to port some servlets
from MS-SQL to PostgreSQL. The servlets contain code like:
rs = stmt.executeQuery("stored_procedure arg1, arg2");
while ( rs.next() )
do_something(rs.getString("col1"), rs.getString("col2"),
rs.getString("col3"), rs.getString("col4") );
So I have to serve my servlet with any kind of datasets and I really
can't imagine, that such a basic task isn't possible with PostgeSQL.
Kind regards
Andreas.
[SQL] Re: Beginner problems with functions
On Thu, 17 Aug 2000, Stephan Szabo wrote:
> What you may need to do is declare a variable of type record
> and do SELECT INTO * From ... rather than just
> the SELECT.
Thanks, that worked.
> Yeah, setof seems fairly wierd. SETOF basetype if
> you do a SELECT FROM table seems to work though.
> I sort of expected that the ones in the regression test would
> either do something understandable or at least error if they
> are testing for brokenness.
Is there any kind of documentation how to cope with that problem?
I try to explain my problem once more:
My servlets contain code like:
rs = stmt.executeQuery("stored_procedure arg1, arg2");
while ( rs.next() )
do_something(rs.getString("col1"), rs.getString("col2"),
rs.getString("col3"), rs.getString("col4") );
I have to decide:
1) Can I use PostgreSQL for such querys?
a) If yes, could someone give any pointer to docs/examles/something else
b) If no, please tel me so. That would let switch to b) or c)
2) Backport the MS SQL server functions into plain SQL text inside
my servlets just to get them working. Not very clever, but should
work, hopefully.
3) Use another database server, could be Interbase but I would prefer
PostgreSQL.
Could somebody please help me to decide which strongle depends from the
SQL procedure problem.
Kind regards
Andreas.
[SQL] Re: Beginner problems with functions
On Mon, 21 Aug 2000, Stephan Szabo wrote: > I haven't thought of an elegant way to do it, although you could > fake some of it with a table of the appropriate structure with a sequence. > It's really ugly, but the pl/sql(tcl/perl/etc...) function gets the > next value of the sequence and inserts the results into a table with > the sequence number and returns the number to you. OK, this might probably work for the short time. Could someone give me any hope for the future that there will be other solutions in higher versions of PostgreSQL which support the missing feature? Kind regards Andreas.
[SQL] Date of creation and of change
Hello, could someone enlighten a fairly beginner how to define columns of a table with the following features: CreateDate DEFAULT value should store current date and time ChangeDate DEFAULT value at creation as above and a Trigger function which stores data end time of any change to the data set I hope someone has this quite usual feature handy or at least a pointer where this is described. Thank you very much Andreas.
[SQL] Re: Using SETOF in plpgsql function
On Wed, 23 Aug 2000, Jan Wieck wrote: > You can't. Not with PL/pgSQL nor with any other PL or C. The > problem is nested deeper and requires the planned querytree > redesign to get solved. > > Before you ask: The mentioned redesign will NOT be done for > 7.1, and I'm not sure if we will be able to do it for 7.2 > yet. Just to make sure you've got to know that this is a feature needed by many users: Add me to the list of users who have a big need for this! Kind regards Andreas.
[SQL] Re: Date of creation and of change
On Wed, 23 Aug 2000, hlefebvre wrote:
> create table mytable( CreateDate timestamp default timestamp('now'),
> );
Thanks, this works.
> CREATE FUNCTION myt_stamp () RETURNS OPAQUE AS
> BEGIN
> ChangeDate := timestamp(''now'');
> RETURN NEW;
> END;
> ' LANGUAGE 'plpgsql';
I tried:
web=# CREATE FUNCTION changed_at_timestamp () RETURNS OPAQUE AS '
web'# BEGIN
web'# ChangeDate := timestamp(''now'');
web'# RETURN NEW;
web'# END;
web'# ' LANGUAGE 'plpgsql';
CREATE
web=# select changed_at_timestamp () ;
ERROR: typeidTypeRelid: Invalid type - oid = 0
web=#
Is this just the wrong way to test the function?
As a beginner I try to validate each new step I do and so I wonder
if I insert the Trigger you mentioned
> CREATE TRIGGER myt_stamp BEFORE INSERT OR UPDATE ON mytable
> FOR EACH ROW EXECUTE PROCEDURE myt_stamp();
the function could cause errors.
Sorry, I'm not very familiar with this function stuff :-(.
Kind regards
Andreas.
[SQL] Re: Date of creation and of change
On Wed, 23 Aug 2000, hlefebvre wrote:
> create table mytable( CreateDate timestamp default timestamp('now'),
> );
I've done a pg_dump and there this line was transformed to:
"createdat" timestamp DEFAULT '23.08.2000 15:35:16.00 CEST'::"timestamp",
I'm afraid if I ever should use this dump as a restore the following
dates will be wrong, thought. So is there a save way to use in the
dumps?
May be the suggestion of Stuart <[EMAIL PROTECTED]>
On Wed Aug 23 14:36:56 2000
> On insert, however, this will do the job.
>
> Create table fred (joe integer, createdtime datetime not null default text
> 'now');
>
> If you dont put the text in you get the date the table was created in all
> future inserts. The text force the current now to be used.
> ?Is this fixed in 7.0.x
Would do a better job in this case?
What's wrong here?
Kind regards
Andreas.
[SQL] Create table in functions
Hello, I striped down my function up to a strange thing: web=# create function pHelpTable( ) web-# returns int web-# As ' web'# Begin web'# Create Table Temp_NumTable ( Num int ) ; web'# web'# return 17 ; web'# End; ' web-# language 'plpgsql' ; CREATE web=# web=# select pHelpTable( ); ERROR: copyObject: don't know how to copy 611 web=# What does this mean? The ERROR is caused by the Create Table statement (when I removed it from my complex function it worked well). So why doesn't this work and what copy function fails here?? Kind regards Andreas.
[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.
[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.
[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.
[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.
[SQL] Argument variables for select
Hello, I want to use the following construct in a stored function: Create Function VarSelect ( varchar, varchar ) returns int As ' Declare num int ; Begin Select Into num Count(*) From $1 Where $2 ; return num; End ; ' language 'plpgsql' ; Could someone please explain who to type the exact syntax so that I can ship the table to select from as $1 and the condition to select what as $2? Or do I have to concatenate a string with the whole select statement and how to call this string? In MS SQL server this could be done with Exec ( query ) Kind regards Andreas.
[SQL] Re: Argument variables for select
On Mon, 28 Aug 2000, Yury Don wrote: > Andreas Tille wrote: > > > > Create Function VarSelect ( varchar, varchar ) > >returns int > >As ' > > Declare num int ; > > > > Begin > >Select Into num Count(*) From $1 Where $2 ; > >return num; > > End ; > >' language 'plpgsql' ; > > > AFAIK it's impossible with plpgsql, but it's possible in pltcl. Hmmm, I wonder how many languages I will learn while dealing with PostgreSQL. What about performance of pltcl compared to C. I wonder if I just do all my work using C-functions, because I know C very well and don't want to reach the next limit which I will possibly face when using pltcl. I would really like to write all my functions in SQL or PLPGSQL. If this is really impossible (I just wonder if the construct above could really not be implemented???), I would prefer C over other languages, if there are no real drawbacks. Kind regards Andreas.
[SQL] Re: Argument variables for select
On Mon, 28 Aug 2000, Yury Don wrote: > > Create Function VarSelect ( varchar, varchar ) > >returns int > >As ' > > Declare num int ; > > > > Begin > >Select Into num Count(*) From $1 Where $2 ; > >return num; > > End ; > >' language 'plpgsql' ; > > > > Could someone please explain who to type the exact syntax so that > > I can ship the table to select from as $1 and the condition to select > > what as $2? Or do I have to concatenate a string with the whole > > select statement and how to call this string? In MS SQL server this > > could be done with > >Exec ( query ) > > > AFAIK it's impossible with plpgsql, but it's possible in pltcl. It is really hard to believe that I'm in so very deep trouble with PostgreSQL. It seems to me that stored procedures are far from beeing as usefull as I learned them to know in MS SQL server. Once I try to follow one hint I'm standing in frot of the next even harder problem. web=# create function testfunc( ) web-# returns int web-# As ' web'# spi_exec "SELECT count(*) AS $num FROM testtable" web'# web'# return $num ; web'# End; ' web-# language 'pltcl' ; ERROR: Unrecognized language specified in a CREATE FUNCTION: 'pltcl'. Recognized languages are sql, C, internal and the created procedural languages. I have installed the pgtcl package of my Debian distribution, so I guess it should be available. Once more the question: Is it really necessary to use a further interpreter instead of sticking with SQL commands to use the original problem. I have to admit that my boss wonders why I'm switching from a working solution (MS SQL) to so much trouble :-(. I really hope to convince him to OpenSource but it's much harder than I expected. Kind regards Andreas.
[SQL] Re: RE: Create table in functions
On Thu, 31 Aug 2000, Jan Wieck wrote: First of all thank you very much for your effort! This is definitely a good argument for PostgeSQL to have such fast support in adding features which might convince some of my collegues or my boss :). > Could you try out the current CVS sources? Well, I walked around the problem and I'm affrais I don't want to use the CVS tree in a production environment. Moreover I have very poor bandwith and I'm sitting behind a CVS disabled firewall :(. So I have problems to check it soon. May be I coudl try a patch against 7.0.2 sources if this would not make much trouble. I definitely will honor your effort by testing it but I can't do this in the next weeks! > I've added an EXECUTE keyword to PL/pgSQL earlier - er - > yesterday. It should give you the power to at least port the > proc below to PostgreSQL. Hopefully some other PostgreSQL users besides me will love you for that . Kind regards Andreas.
[SQL] Order by in stored functions
Hello I tried to create the following function CREATE Function pGetMenu ( int ) returns setof varchar As ' SELECT IdMenuShow || IdWebPage FROM Menu WHERE IdMenu = $1 ORDER BY IdSort ; ' language 'SQL' ; I've got the following error message: ERROR: function declared to return varchar returns multiple values in final retrieve If O just remove the ORDER BY clause als works well so I guess that the ORDER BY has to be replaced by somethjing other. Could somebody enlighten me how to do the ordering in a stored procedure? Kind regards Andreas.
[SQL] Re: Order by in stored functions
On Mon, 4 Sep 2000, Tom Lane wrote: > This is a bug that has already been fixed in current sources: the check > that makes sure your select produces only one varchar column is > mistakenly counting the hidden IdSort column that's needed to sort by. Is there any patch against 7.0.2 sources which might help me (or the Debian package maintainer out? > I don't know of any good workaround in 7.0, short of patching the > erroneous code. Have you thought about using a view, rather than a > function returning set? I could try that. Is there any general advise for more or less beginners like me regarding when to use views and when to use functions? Kind regards Andreas.
[SQL] Re: Returning Recordsets from Stored-procs
On Mon, 6 Nov 2000, Grant Finnemore wrote: > Whilst this is an unfortunate position at the moment, it has been my experience that >it does not cause insurmountable problems. > (Some short term headaches - yes. ;-) After learning this as a fact you have short term headaches but before you have continuos headache while trying to port a database. I think I'm not the only one who would be really, really happy if *real* stored procedures would be high on top of the todo list. (In fact this is the *only* thing I'm currently really missing in PostgreSQL.) Kind regards Andreas.
[SQL] Re: Requests for Development
On Thu, 9 Nov 2000, Josh Berkus wrote: > 2. Stored Procedure functionality, i.e. outputting a full recordset from > a function (or new structure, if functions are hard to adapt) based on > the last SELECT statement passed to the function. An alternative would > be to develop parameterized views, which might be the easiest path. I'm not really sure if parameterized views are a real alternative. They would help in some cases, but *real* stored procedures would be much more powerful. In my opinion it is also in the sense of easier porting from databases to PostgreSQL to the benefit od PostgreSQL. I wonder if there couldn't borrowed some code from Interbase which has full featured stored procedures - at least it was told to me that it has ... > 3. Slightly more informative syntax error messages - frankly, just > grabbing a little more text around the word or punctuation that > triggered the error would be enormously helpful (I can't tell you the > number of times I've gotten "Error at or near ')'" in a huge DDL > statement. Waht about i18n. Could PostgreSQL sources gettext-ized? > Thanks so much for your ongoing hard work! Couldn'trepeated often enough alos for the past! Kind regards Andreas.
[SQL] Foreign key problem
Hello, I tried to track down the database definitions from a more complex database which I have to convert from MS SQL to PostgreSQL. I have only one last syntactical error. Here is the striped down code to the shortest snipped which shows the problem: CREATE TABLE ResKulturDetail ( IdLabNr int, IdIndex smallint ); CREATE TABLE ResKulturDetailDay ( IdLabNr int, IdIndex smallint ); CREATE INDEX IX_IdLabNr_KulturDetail ON ResKulturDetail(IdLabNr) ; CLUSTER IX_IdLabNr_KulturDetail ON ResKulturDetail ; ALTER TABLE ResKulturDetailDay ADD CONSTRAINT FK_ResKulturDetailDay FOREIGN KEY (IdLabNr,IdIndex) REFERENCES ResKulturDetail (IdLabNr,IdIndex) ; Here is the psql log, if I try to insert the code above: reskultur=# CREATE TABLE ResKulturDetail reskultur-# ( reskultur(# IdLabNr int, reskultur(# IdIndex smallint reskultur(# ); CREATE reskultur=# reskultur=# CREATE TABLE ResKulturDetailDay reskultur-# ( reskultur(# IdLabNr int, reskultur(# IdIndex smallint reskultur(# ); CREATE reskultur=# reskultur=# CREATE INDEX IX_IdLabNr_KulturDetail ON ResKulturDetail(IdLabNr) ; CREATE reskultur=# CLUSTER IX_IdLabNr_KulturDetail ON ResKulturDetail ; CLUSTER reskultur=# reskultur=# ALTER TABLE ResKulturDetailDay ADD CONSTRAINT FK_ResKulturDetailDay reskultur-# FOREIGN KEY (IdLabNr,IdIndex) reskultur-# REFERENCES ResKulturDetail (IdLabNr,IdIndex) ; NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: UNIQUE constraint matching given keys for referenced table "reskulturdetail" not found Can anybody explain, why the foreign key constraint fails? Thanks and have a nice weekend Andreas. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Explicite typecasting of functions
Hello, I want to insert new data into a table with increasing data set ids. The table has two separate "regions" of data: Those with Ids below 100 and other. If I want to create a new Id in the "lower region" I tried the following (simplified example): CREATE TABLE Items ( Idint DEFAULT NextItem() ) ; /* ERROR: Function 'nextitem()' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts */ CREATE FUNCTION NextItem() RETURNS INT4 AS 'select max(Id)+1 from Items where Id < 100;' LANGUAGE 'sql'; I did not found any trace of documentation how to do an explicit typecast for the function. Defining the function first fails because: ERROR: Relation "items" does not exist Any hint to solve this kind of chicken-egg-problem? Kind regards Andreas. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Explicite typecasting of functions
On Wed, 14 Aug 2002 Richard Huxton wrote:
> > CREATE TABLE Items (
> > Idint DEFAULT NextItem()
>
> > CREATE FUNCTION NextItem() RETURNS INT4
> > AS 'select max(Id)+1 from Items where Id < 100;'
> > LANGUAGE 'sql';
>
> >ERROR: Relation "items" does not exist
> >
> > Any hint to solve this kind of chicken-egg-problem?
>
> Your solution is not safe anyway - you could end up with two processes trying
> to insert the next value.
While you are perfectly right in principle I can be sure that this can not
happen in this application.
> Can I suggest two sequences: item_low_seq and item_high_seq? Set the initial
> value of each to 1,000,000 and 99,000,000 (or whatever) and then use
> whichever sequence is appropriate.
>
> In the example above you'd want something like:
> id int not null default nextval('item_low_seq')
In fact I want to let PostgreSQL manage only the Ids of the 'lower region'
via sequences/functions whatever. The 'higher region' is imported from
an external source and contains explicite Ids.
But anyway for academic reasons: What means adding 'explicit typecast'
of a func???
I was not able to find this term in the docs.
Kind regards
Andreas.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Explicite typecasting of functions
On Wed, 14 Aug 2002, Tom Lane wrote: > I concur with Richard that Andreas needs to solve a different problem, > but just for the record, the way you could do it is > > CREATE TABLE without mentioning the default > > CREATE FUNCTION > > ALTER TABLE ... SET DEFAULT > > Note however that pg_dump is not bright enough to deduce that you did > this. It will dump the table definition first, with the DEFAULT clause, > and so you'll have to do manual surgery on the dump file if you ever > need to reload. Hmmm, the original problem I wanted to solve using this function is that pg_dump is not bright enough to store sequences. I use a development machine to build the database do a pg_dump and after this I try to put this dump to the production (readonly) server. This procedure every time requires manual setting of the sequences. I tried to avoid this by the function. Any better way to do this? Kind regards Andreas. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Separating data sets in a table
Hello, I want to solve the following problem: CREATE TABLE Ref( Id int ) ; CREATE TABLE Import ( Idint, Other varchar(42), Flag int, Tstimestamp ) ; CREATE TABLE Data ( Idint, Other varchar(42) ) ; The table Import will be filled by a COPY FROM statement and contains no checks for referential integrity. The columns Id and Other have to be moved to the table Data if the table Ref contains the Id. If not Flag should get a certain value that something went wrong. Moreover Import should only contain one representation of a dataset with equal Id and Other column and I would like to store the newest one (this is the reason for the timestamp). I tried to do the following approach: CREATE LOCAL TEMPORARY TABLE ImportOK () INHERITS (Import) ; INSERT INTO ImportOK SELECT * FROM Import i INNER JOIN Ref r ON i.Id = r.Id; DELETE FROM Import WHERE Id IN (SELECT Id FROM ImportOK) ; The idea was that the latest statement should get rid of all valid data sets from Import. The valid datasets now could be moved to Data and I could afterwards check Import for duplicated data sets. Unfortunately the latest statement is so terribly slow that I can't imagine that there is a better way to do this. It seems like a very beginner question but I have no real clue how to do this right. Probably the solution has to be done completely different. Thanks for your patience Andreas. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Separating data sets in a table
On Sat, 24 Aug 2002, Mark Stosberg wrote: > On Thu, 22 Aug 2002, Andreas Tille wrote: > > Hello, > > > > I want to solve the following problem: > > > > CREATE TABLE Ref( Id int ) ; > > CREATE TABLE Import ( Idint, > > Other varchar(42), > > Flag int, > > Tstimestamp ) ; > > CREATE TABLE Data ( Idint, > > Other varchar(42) ) ; > larger problem. I get the sense that you have data you importing on a > regular basis from outside Postgres, and you want to check it before > it get moves into production, but I'm not exactly sure what's happening. You are completely right. I just do an import from an external database. The person I obtain the data from does an output of the table in a form to do a "COPY FROM". The problem is that it might happen that there are some data rows which infringe referential integrity and I have to ask back the data provider for additional data which describe additional data which are referenced by the Id mentioned above. So I have to sort out those data sets who have no known Id in my production data. Kind regards Andreas. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Separating data sets in a table
On Sun, 25 Aug 2002, Mark Stosberg wrote: > Thanks for the clarification. Here's an idea about how to solve your > problem. As you are importing your data, instead of doing it all at > once, try import it a row at a time into a table that has the RI turned > on. Check each insert to see if it's successful. It if it's not > successful, then insert that row into a table that /doesn't/ have RI > (maybe "import_failures"), > perhaps also including the error that Postgres returned. (This may be > stored in $DBH::errstr). Then when you are done, you can look in the > import_failures for a report of which rows need some assistance. If you > need every row to succeed that's imported into the production table, you > can do all this inside of a transaction, and roll it back if any of the > inserts fail. [ thinks for a moment. ] Of course, that would normally > rollback your inserts into import_failures too, so perhaps you can use a > second database connection to make sure those always happen. > > I hope that helps. Perhaps thinking in terms of "row-at-a-time > processing" will help you solve your problem. Well for sure this might be an option but as I said I receive the data in the dump format apropriate to use "COPY FROM ". Would you really like to suggest me to split those data sets into single lines? Moreover I'm not sure about how to catch the error messages of failed COPY statements. I've thought that including all data and handling them afterwards would be agood idea and it is just my lack of SQL knowledge which prevents me from finding a clever solution to sort the stuff out. Kind regards Andreas. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Separating data sets in a table
On 26 Aug 2002, Oliver Elphick wrote: > Create a temporary table (no constraints) > > CREATE TEMP TABLE temptable AS > (SELECT * FROM tablename LIMIT 1); > DELETE FROM temptable; > > Copy all data into the temporary table > > COPY temptable FROM 'filepath'; Up to this point I have no problems. The difference is that I have to mark the invalid data sets by a flag which represents a "reason" why the data were invalid. That's why I use an additional flag in the table and I do not use a temporary table because I have to store the "histrory" of invalid data (to bother the provider of the data to fix it). > Select from the temporary table all items that satisfy the > constraints, insert them into the real table and delete them from > the temporary table: > > BEGIN; > INSERT INTO tablename (SELECT * FROM temptable WHERE ...); > DELETE FROM temptable WHERE ...; > COMMIT; > > All good data should now be in place. The temporary table should > now contain only those items that do not satisfy the constraints for > the real table. This was in my first atempt here. The problem I have is that I need a JOIN to a further table and I've got errors from the parser which let me guess that joins are not allowed in INSERT statements ... at least I do not know how to do it right if it should be possible. That's why I had the idea just to set a certain flag and then do the insert of all data sets where flag = OK. Kind regards Andreas. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] [OT] Unsubscribe does not work
Hi, once I subscribed to this list I've got the information how to subscribe: If you ever want to remove yourself from this mailing list, send the following command in email to [EMAIL PROTECTED]: approve unsubscribe Andreas Tille <[EMAIL PROTECTED]> Well, I did so but I'm still reciving messages from this list (as well as from PostgreSQL-general <[EMAIL PROTECTED]> but I wanted to bother the lower volume list at first.) I'll be on vacation for a longer period and do not want my mailbox flooded ... Any help Andreas. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [OT] Unsubscribe does not work
On Tue, 21 Jan 2003, Bruno Wolff III wrote: > What response did you get to your message? No response at all. > I usually use [EMAIL PROTECTED] as the address to send mailing > list commands to, though it is possible the address you tried works > as well. I normally send those command to majordomo as usual for mailing lists, but if I get those explicite advise ... Next try: mailx -s unsubscribe [EMAIL PROTECTED] <<... unsubscribe ... Let's see what happens ... Andreas. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Scaler forms as function arguments
Hi, I want to write a function of the following type CREATE FUNCTION test ( ) RETURNS setof MyTable AS 'SELECT * FROM MyTable WHERE id IN $1' LANGUAGE 'SQL' ; I was not able to find a reference whether this is possible and if yes how to specify the argument type and how to call this function to hand over the list for the IN clause correctly. Next question. Is there any example how to return a SETOF MyTable in a plpgsql function? I tried CREATE FUNCTION test2() RETURNS setof MyTable AS ' DECLARE result SETOF MyTable ; BEGIN result := (SELECT * FROM MyTable); RETURN result ; END; ' LANGUAGE 'plpgsql'; wich failed. Kind regards Andreas. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Scaler forms as function arguments
On Wed, 26 Nov 2003, Richard Huxton wrote: > Not as you've done it. You could pass in text "(1,2,3)", build your query and > use EXECUTE to execute it. This boils down the question to the problem which occured with your promissing link below, because I need to use PL/pgSQL, right? > Alternatively, you might be able to do it with an > array parameter (sorry, I don't use arrays, so I can't be sure). I'll give that a try. > Read the section on plpgsql in the manuals, you return results one at a time. > For some examples, see http://techdocs.postgresql.org/ and look for the "Set > Returning Functions" item. A very interesting article but if I try the example code: create table department(id int primary key, name text); create table employee(id int primary key, name text, salary int, departmentid int references department); insert into department values (1, 'Management'); insert into department values (2, 'IT'); insert into employee values (1, 'John Smith', 3, 1); insert into employee values (2, 'Jane Doe', 5, 1); insert into employee values (3, 'Jack Jackson', 6, 2); create function GetEmployees() returns setof employee as 'select * from employee;' language 'sql'; create type holder as (departmentid int, totalsalary int8); create function SqlDepartmentSalaries() returns setof holder as ' select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid ' language 'sql'; create or replace function PLpgSQLDepartmentSalaries() returns setof holder as ' declare r holder%rowtype; begin for r in select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid loop return next r; end loop; return; end ' language 'plpgsql'; I get: test=# select PLpgSQLDepartmentSalaries() ; WARNING: Error occurred while executing PL/pgSQL function plpgsqldepartmentsalaries WARNING: line 5 at return next ERROR: Set-valued function called in context that cannot accept a set test=# Any hint what might be wrong here? I'm using PostgreSQL 7.3.2 under Debian GNU/Linux (testing). Kind regards Andreas. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Scaler forms as function arguments
On Thu, 27 Nov 2003, Joe Conway wrote: > Andreas Tille wrote: > > test=# select PLpgSQLDepartmentSalaries() ; > > This should be: > regression=# select * from PLpgSQLDepartmentSalaries(); > departmentid | totalsalary > --+- > 1 | 8 > 2 | 6 > (2 rows) Well, it is easy to understand what it 'should be' reading the code - but it throws the error message I posted. Is this possibly a feature of a higher PostgreSQL version than 7.3.2? Kind regards Andreas. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
