Re: [SQL] Does PostgreSQL support job?
> > > I try to find in the documentation whether PostgreSQL > supports job, > > > but I miserably failed. Does PostgreSQL support job? If > not, what > > > is the mechanism mostly adopted by PostgreSQL administrators for > > > running jobs against PostgreSQL? I was thinking about using > > > cron/plsql/sql-scripts on Linux. > > > > The answer really depends on what you mean by "jobs". If > you have a > > database task that can be expressed as a series of commands with no > > interaction involved, you can just put those commands in a file > > (your-job- > > name.sql) and run it using psql and cron: > > > > # replace leading stars with cron time settings > > * * * * * psql your-database -i your-job-name.sql > > > > Yes, that's it. A job is a task, i.e. set of statements, > which is scheduled to run against a RDBMS at periodical > times. Some RDBMS, such as SQL Server and Oracle, support > that feature, even if such a feature is managed differently > from a RDBMS to another. You could look at pgagent, which comes with pgAdmin3 (http://www.pgadmin.org/docs/1.4/pgagent.html). It does some scheduling that's a lot more advanced than you get from plain cron. And nice pgadmin integrated management of course. //Magnus ---(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 through to the mailing list cleanly
Re: [SQL] executing dynamic commands
Thanx a lot guys - it works ! Cheers Chris On Wed, 1 Feb 2006 christian ( dot ) michels ( at ) eifelgeist ( dot ) com wrote: > Hi, > > I user PostgreSQl 8.0.4 on Win2003 Server and write a function to copy rows > from one table into another table with the same column definition. > My first approach was to use something like: > > query_value := 'INSERT INTO ' || tabledest || ' SELECT * FROM ' || tablesrc; > EXECUTE query_value; > > This only works if the column definition AND the order between source and > destination is the same ! > In my case I have always the same column definitions but they are not in> the > same order between source and destination table. > What I tryed then is to loop through the column definition of the source > and query the sourcetable for the value. For that I have to execut a > query with dynamic tablename and dynamic columname to generate two > stings one with the columndefinitin and one with the columnvalues to > exececute something like: INSERT INTO tabelfoo (columndefinitinstring) > VALUES (columnvaluesstring) You might have better luck with a INSERT ... SELECT where you've reordered the columns in the select list INSERT INTO tabledest SELECT FROM tablesrc ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Does PostgreSQL support job?
Hi, Daniel, Daniel Caune wrote: > I'm not sure to understand. Why calling a function from a script is > different from executing a series of SQL commands? I mean, I can run a > script defined as follows: > > SELECT myjob(); > > where myjob is a stored procedure such as: > > CREATE OR REPLACE FUNCTION myjob() > RETURNS void > AS $$ > > END; > $$ LANGUAGE PLPGSQL; > > Does that make sense? It does make sense if myjob() does more than just execute a bunch of statements, e. G. it contains if(), loops or something else. PLPGSQL is turing complete, plain SQL is not. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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 through to the mailing list cleanly
Re: [SQL] Changing the transaction isolation level within the stored
Hi, Andreq, Andrew Sullivan wrote: > I think you don't have a clear idea of what locks are necessary for > updates. Write operations on a row must block other write operations > on the same row. If more than one transaction needs the same kinds > of locks on two different tables, but attempts to get those locks in > the opposite order, you are all but guaranteed a deadlock. MVCC > helps, but it can't avoid locking the same data when that data is > being updated. You're right, I was mislead from my memory. Sorry for the confusion I brought to this issue. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Does PostgreSQL support job?
> > I'm not sure to understand. Why calling a function from a script is > different from executing a series of SQL commands? I mean, I can run a > script defined as follows: > > > > SELECT myjob(); > > > > where myjob is a stored procedure such as: > > > > CREATE OR REPLACE FUNCTION myjob() > > RETURNS void > > AS $$ > > > > END; > > $$ LANGUAGE PLPGSQL; > > > > Does that make sense? > > It does make sense if myjob() does more than just execute a bunch of > statements, e. G. it contains if(), loops or something else. > > PLPGSQL is turing complete, plain SQL is not. > Yes, indeed, that was the idea! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Does PostgreSQL support job?
Hi, Daniel, Daniel Caune wrote: >>> I'm not sure to understand. Why calling a function from a script is >>> different from executing a series of SQL commands? [snip] >>>Does that make sense? >>It does make sense if myjob() does more than just execute a bunch of >>statements, e. G. it contains if(), loops or something else. >>PLPGSQL is turing complete, plain SQL is not. > Yes, indeed, that was the idea! There's another reason: For updating the cron job SQL commands, you need root access (or at least shell access) to the database machine. For updating a stored procedure, you need just the appropriate rights in the database. On larger deployments, this can be an important difference. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Does PostgreSQL support job?
> Daniel Caune wrote:
> >>> I'm not sure to understand. Why calling a function from a script
is
> >>> different from executing a series of SQL commands?
>
> [snip]
> >>>Does that make sense?
> >>It does make sense if myjob() does more than just execute a bunch of
> >>statements, e. G. it contains if(), loops or something else.
> >>PLPGSQL is turing complete, plain SQL is not.
> > Yes, indeed, that was the idea!
>
> There's another reason: For updating the cron job SQL commands, you
need
> root access (or at least shell access) to the database machine. For
> updating a stored procedure, you need just the appropriate rights in
the
> database.
>
> On larger deployments, this can be an important difference.
>
You are absolutely right. That is such detail I was thinking over.
Managing stored procedures into a RDBMS seems less laborious than
modifying some SQL scripts on the file system. I mean there is always a
need to define initially a script, run by the cron/psql couple, which
calls a stored procedure responsible for doing the job ("SELECT
myjob();"). Therefore it is easier to modify implementation details of
the job without having to modify the script run by the cron/psql. On
another hand, it seems easier to test modification by patching a stored
procedure directly in the RDBMS and making some tests on-the-fly.
--
Daniel CAUNE
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Does PostgreSQL support job?
O Markus Schaber έγραψε στις Feb 2, 2006 : > Hi, Daniel, > > Daniel Caune wrote: > > > I'm not sure to understand. Why calling a function from a script is > > different from executing a series of SQL commands? I mean, I can run a > > script defined as follows: > > > > SELECT myjob(); > > > > where myjob is a stored procedure such as: > > > > CREATE OR REPLACE FUNCTION myjob() > > RETURNS void > > AS $$ > > > > END; > > $$ LANGUAGE PLPGSQL; > > > > Does that make sense? > > It does make sense if myjob() does more than just execute a bunch of > statements, e. G. it contains if(), loops or something else. > > PLPGSQL is turing complete, plain SQL is not. H is SQL equally powerful as a pushdown automaton then??? Just kidding! > > Markus > -- -Achilleus ---(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 through to the mailing list cleanly
Re: [SQL] Does PostgreSQL support job?
H, Achilleus, Achilleus Mantzios wrote: >>PLPGSQL is turing complete, plain SQL is not. > H is SQL equally powerful as a pushdown automaton then??? SQL is _not_ a programming language, it is a query language. It is not meant to be turing complete. Just as e. G. HTML, CSS or RFC2822 are structural or layout languages, but not programming languages. > Just kidding! Now, you're kidding. :-) HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Does PostgreSQL support job?
O Markus Schaber έγραψε στις Feb 2, 2006 : > H, Achilleus, > > Achilleus Mantzios wrote: > > >>PLPGSQL is turing complete, plain SQL is not. > > H is SQL equally powerful as a pushdown automaton then??? > > SQL is _not_ a programming language, it is a query language. It is not > meant to be turing complete. > > Just as e. G. HTML, CSS or RFC2822 are structural or layout languages, > but not programming languages. > > > Just kidding! > > Now, you're kidding. :-) Well, if we add one stack to SQL it will kick some major PASCAL ass! > > HTH, > Markus > -- -Achilleus ---(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 through to the mailing list cleanly
[SQL] strange quoted csv behavior with COPY
what would you expect the following command to insert into column a: copy foo (a,b) from stdin with csv; "bar" , 3 \. i was expecting to see 'bar', but instead i get 'bar ' (the spaces between the double quote and the comma get inserted. select length(a), * from foo; length | a| b ++--- 6 | bar| 3 is this by design? what is the use of the quote in this context? i am on 8.0.6. george ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL]
hi every body, how to return the resultset from the function
