[SQL] sql query
sir , kindly send me the solution of the above query. 1. To calculate th no. of days between 01/01/2000 to 03/02/2001. 2. TO calculate th time in newfoundland from centra standard time from 02-22-97 ,05.00 am. _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
[SQL] Re: plpgsql grief
rob wrote:
> Hi, I'm having some real headache problems here. Apologies for the
> length, i just want to get it all out now :)
>
> I figured moving some 'simple' db code from my application to it's more
> natural home in the db would work out. Bummer. Not only do i have to run
> 7.1 (beta 4) to be able to dynamically generate queries, I'm finding it
> *extrememly* difficult to get to get my simple functions to work (plus
> for the 'widest used open source db' i'm finding examples very hard to
> come by)
>
> Before I start if anyone has any pointers to coding examples (and I mean
> a little more than the standard postgres docs :) I'd be eternally
> greatful. Failing that, can anyone help with these two simple (ahem)
> codelets :
>
> Example 1 :
>
> create function testfunc (text) returns int4 as '
> declare
> sql varchar;
> res int4;
> begin
> sql=''SELECT INTO res2 id FROM ''||$1 ;
> execute sql ;
> return res;
> end;
> ' language 'plpgsql' ;
>
> simple function to return the id field of a table (passed to the
> function). ok, not a real world example, however i do this :
>
> #select testfunc('tablenam') ;
> and i get
> ERROR: parser: parse error at or near "into"
>
> ok this is actually first things last. I'm not really bothered about
> returing values into local variables and then returning them, it's just
> a run through. If I can't get this right, what chance have i got at
> sorting out the real work i want to do.
>
> Example 2 :
>
> create function update_trans (text, integer, text, text, text, text,
> text) returns boolean as '
> declare
> tbl alias for $1 ;
> begin
> execute ''insert into tbl (objid, objtbl, et, event, time, reason,
> owner) values ($2, $3, $4, $5, now(), $6, $7)'';
> return 0;
> end;
> ' language 'plpgsql' ;
>
> # select update_trans('tablname','1'
> ,'sometext','sometext','sometext','sometext','sometext') ;
> ERROR: Relation 'tbl' does not exist
>
> dur. yeah i know it doesn't exist cause i want to pass it in parameter
> 1. Tried substituting tbl with $1 and quote_ident($1) and
> quote_ident(tbl) in the sql string, but that didn't work either. (BTW
> anyone know of any GUI interfaces that support 7.1 - phpPgAdmin 2.1,
> 2.2.1 and 2.3 seem to balk on functions)
>
> Example 2 is prelude to a larger function (not much larger - but then
> this is relavitve to how easy to code it is) to monitor the changes made
> by a user, what they change from and to and who/when/why this is
> already implemented in my app code - PHP - and checking out the features
> available in postgres i figured i could do some kind of looping through
> the OLD and NEW dataset-array things, comparing them against each other,
> sorta like this :
>
> for ($i = 0 ; $i < count($NEW) ; $i++) {
> /* since $NEW and $OLD are essentially the same we can do this */
> if ($OLD[$i] != $NEW[$i])
> record the change bla bla bla
>
> }
> I'm really hoping I can, as at this rate I've spent the better part of
> three days trying to figure the simple things above out and the only
> thing i'm about to reach is breaking point...
>
> Sorry for the sarcasm, I'm about to pop.
>
> Rob
I feel your pain;^)
Here is the text of a post from Tuesday... I think it answers your question
which is that you cannot do variable subsititution for table or field names
inside procedures. This is not a Postgres specific limitation, MS SQL
Server has the same issue.
> > "DR" == David Richter
> writes:
>
> DR> Folks,
> DR> I wrote that function, wich doesn't
> work. I want to hand over the name
> DR> of the tables(relation_table,
> update_table) and a
> DR> column(column_to_fill). The intention
> is, to use the function also with
> DR> other tables(not hard coded).
>
> DR> BUT this error appears :
> DR> psql:restructure.sql:32: ERROR: parser:
> parse error at or near "$1"
>
> DR> I didn't found any solution.
> DR> I would be grateful , if I could get
> some more Examples(more than in the
> DR> Docu of www.postgresql.org and Bruce
> Monjiam's Book) about parameters in
> DR> PL/PGSQL - functions.
> DR> I would be no less grateful if anybody
> give detailed suggestions.
>
> DR> CREATE FUNCTION
> patient_study_restructure (text,text,text)
> RETURNS
> DR> integer AS '
> DR> DECLARE
>
> DR> relation_table ALIAS FOR $1;
> DR> update_table ALIAS FOR $2;
> DR> column_to_fill ALIAS FOR $3;
> DR> psr_rec record;
> DR> bound integer;
> DR> i integer := 0;
>
> DR> BEGIN
> DR> FOR psr_rec IN SELECT * FROM
> relation_table LOOP
> DR> UPDATE update_table
> DR> SET column_to_fill = psr_rec.parentoid
> DR> WHERE chilioid = psr_rec.childoid;
> DR> i := i + 1;
> DR> END LOOP;
> DR> IF NOT FOUND THEN RETURN 1;
> DR> ELSE RETURN i;
> DR> END IF;
> DR> END;
>
> DR> ' LANGUAGE 'plpgsql';
>
> DR> SELECT
> DR>
> patient_study_restructure('relpatient_study000','study','patientoid');
>
>
> DR> Anybody (Jan Wieck?) who can make some
> sugestions on
> DR> the above will
> DR> receive my enthusiastic gratitud
Re: [SQL] parse error in create index
Stephan Szabo wrote:
>
> Functional indexes cannot currently take constant values to the function,
> so it's complaining about the constant 'month'. The current workaround is
> probably to create a function that does the date_part('month', ) for
> you and then use that function in the index creation.
Hmm... Perhaps, it's better I post to the novice group, because I'm new
to SQL.
Anyway -- That's my trial:
adressen=> CREATE FUNCTION geb_monat (date) RETURNS integer AS
adressen-> 'SELECT date_part('month', $1)::integer;'
adressen-> LANGUAGE 'sql';
ERROR: parser: parse error at or near "month"
The point are the nested strings, I guess. How can I render a "'" in an
SQL string?
Thanks for your help!
--
Hubert Palme
[EMAIL PROTECTED]
Re: [SQL] parse error in create index
Stephan Szabo wrote:
>
> Functional indexes cannot currently take constant values to the function,
> so it's complaining about the constant 'month'. The current workaround is
> probably to create a function that does the date_part('month', ) for
> you and then use that function in the index creation.
OK, I got it now -- good old pascal/FORTRAN fashion. But now I get
adressen=> CREATE INDEX xxx ON geburtstage (geb_monat(geburtstag));
ERROR: DefineIndex: (null) class not found
adressen=>
What is a class in this sense, and where can I read about it in the
documentation?
(geburtstag is a row of type DATE in the table geburtstage)
--
Hubert Palme
[EMAIL PROTECTED]
[SQL] Index problem...
I want to query this... --> SELECT * FROM bbs ORDER BY ref desc, step ASC LIMIT 12; this query doesn't refer the index that made by this query. --> CREATE INDEX idx_bbs ON bbs (ref, step); but, i change the query that "ref desc" to "ref asc". then query refer the index, and i can see a result very fast. :-( so, i want to view an result that one column ordered by ascending, and oterh column ordered by descending using index. what do i do? how make an index? == == ¿ì¸® ÀÎÅͳÝ, Daum Æò»ý ¾²´Â ¹«·á E-mail ÁÖ¼Ò ÇѸÞÀÏ³Ý Áö±¸ÃÌ ÇÑ±Û °Ë»ö¼ºñ½º Daum FIREBALL http://www.daum.net
[SQL] PL/PGSQL Cook Book
I've just spent the last day or two trying to get to grips with plpgsql and can't believe how abysmal the documetentation and examples are. I've been trawling through the mailist lists and I notice there was talk back in 1999 abouta PLPGSQL Cook Book - did anything come of this? If no one is maintaining something like this and people think its a good idea I think we should have another crack at it. I'd be happy to maintain something like this and put it up on the web, although I'm only a newbie and would rely upon user contribution. Here are some possible sections to help get people thinking. Even if you don't know the answer send me the questions and I'll add them to the list. How can I create Tree structures? Are recursive functions supported? Whats are the advanteds disadvantes of the different scipting langues PL/PGSQL, PL/Perl, PL/Tcl? How do variable scopes work in PL/PGSQL? Can I output variables from a function to the command line for debugging purposes? How to debug PL/PGSQL? Various examples for each of the statements Anyway lets discuss this, a lot could be done just from piecing together relavent tips from this mailing list. ie there are some good posts on tree structures, which if I'm willing to piece together if people think this project is worth while. Regards Mark
[SQL] plpgsql grief
Hi, I'm having some real headache problems here. Apologies for the
length, i just want to get it all out now :)
I figured moving some 'simple' db code from my application to it's more
natural home in the db would work out. Bummer. Not only do i have to run
7.1 (beta 4) to be able to dynamically generate queries, I'm finding it
*extrememly* difficult to get to get my simple functions to work (plus
for the 'widest used open source db' i'm finding examples very hard to
come by)
Before I start if anyone has any pointers to coding examples (and I mean
a little more than the standard postgres docs :) I'd be eternally
greatful. Failing that, can anyone help with these two simple (ahem)
codelets :
Example 1 :
create function testfunc (text) returns int4 as '
declare
sql varchar;
res int4;
begin
sql=''SELECT INTO res2 id FROM ''||$1 ;
execute sql ;
return res;
end;
' language 'plpgsql' ;
simple function to return the id field of a table (passed to the
function). ok, not a real world example, however i do this :
#select testfunc('tablenam') ;
and i get
ERROR: parser: parse error at or near "into"
ok this is actually first things last. I'm not really bothered about
returing values into local variables and then returning them, it's just
a run through. If I can't get this right, what chance have i got at
sorting out the real work i want to do.
Example 2 :
create function update_trans (text, integer, text, text, text, text,
text) returns boolean as '
declare
tbl alias for $1 ;
begin
execute ''insert into tbl (objid, objtbl, et, event, time, reason,
owner) values ($2, $3, $4, $5, now(), $6, $7)'';
return 0;
end;
' language 'plpgsql' ;
# select update_trans('tablname','1'
,'sometext','sometext','sometext','sometext','sometext') ;
ERROR: Relation 'tbl' does not exist
dur. yeah i know it doesn't exist cause i want to pass it in parameter
1. Tried substituting tbl with $1 and quote_ident($1) and
quote_ident(tbl) in the sql string, but that didn't work either. (BTW
anyone know of any GUI interfaces that support 7.1 - phpPgAdmin 2.1,
2.2.1 and 2.3 seem to balk on functions)
Example 2 is prelude to a larger function (not much larger - but then
this is relavitve to how easy to code it is) to monitor the changes made
by a user, what they change from and to and who/when/why this is
already implemented in my app code - PHP - and checking out the features
available in postgres i figured i could do some kind of looping through
the OLD and NEW dataset-array things, comparing them against each other,
sorta like this :
for ($i = 0 ; $i < count($NEW) ; $i++) {
/* since $NEW and $OLD are essentially the same we can do this */
if ($OLD[$i] != $NEW[$i])
record the change bla bla bla
}
I'm really hoping I can, as at this rate I've spent the better part of
three days trying to figure the simple things above out and the only
thing i'm about to reach is breaking point...
Sorry for the sarcasm, I'm about to pop.
Rob
[SQL] view does not show all records it should
I set up a simple database in wich all works well, exept one *very* strange (to me) result. Some time ago I created this views: CREATE VIEW mag_scaricati_view AS SELECT s.id_carico, SUM(s.qta_scaricata) AS Scaricati FROM mag_scarico_tbl s GROUP BY s.id_carico; CREATE VIEW mag_giacenza1_view AS SELECT c.ref AS "Ref.", p.descrizione_breve AS Descrizione, c.id_carico AS "Scheda di carico", c.qta_caricata AS "Caricati", s.Scaricati, (c.qta_caricata-s.Scaricati) AS "Giacenza" FROM mag_carico_tbl c, mag_scaricati_view s , prd_ref_tbl p WHERE (c.id_carico = s.id_carico) AND (c.ref = p.ref); Now, when I issue the following command: SELECT * FROM mag_giacenza1_view it shows me the records that was in the database the day I created the view, _not all the records in the database_. If I select "by hand" with the following statament (please note it is the very same with which I created the view): SELECT c.ref AS "Ref.", p.descrizione_breve AS Descrizione, c.id_carico AS "Scheda di carico", c.qta_caricata AS "Caricati", s.Scaricati, (c.qta_caricata-s.Scaricati) AS "Giacenza" FROM mag_carico_tbl c, mag_scaricati_view s , prd_ref_tbl p WHERE (c.id_carico = s.id_carico) AND (c.ref = p.ref); it shows me all the records. Please, is there anybody who can help me, I can't even imagine what's happening. :-((( Ciao. -- Giovanni Biscuolo mailto:[EMAIL PROTECTED]
Re: [SQL] parse error in create index
Stephan Szabo wrote:
>
> Functional indexes cannot currently take constant values to the function,
> so it's complaining about the constant 'month'. The current workaround is
> probably to create a function that does the date_part('month', ) for
> you and then use that function in the index creation.
Hmm... Perhaps, it's better I post to the novice group, because I'm new
to SQL.
Anyway -- That's my trial:
adressen=> CREATE FUNCTION geb_monat (date) RETURNS integer AS
adressen-> 'SELECT date_part('month', $1)::integer;'
adressen-> LANGUAGE 'sql';
ERROR: parser: parse error at or near "month"
The point are the nested strings, I guess. How can I render a "'" in an
SQL string?
Thanks for your help!
--
Hubert Palme
[EMAIL PROTECTED]
[SQL] Re: plpgsql grief
> > I feel your pain;^) > > Here is the text of a post from Tuesday... I think it answers your question > which is that you cannot do variable subsititution for table or field names > inside procedures. This is not a Postgres specific limitation, MS SQL > Server has the same issue. > > > > "DR" == David Richter > > writes: Ya know, i already read this one. That's what got me on to 7.1 and using EXECUTE :) ARgh! Apparenty I've opened up a real can of worms with wanting cool general functions, with a bit of dynamism and business logic. However OK dumped pl/pgsql, thinking pl/tcl is more my bag (didn't fancy recompiling perl to get the shared lib, and didn't want to waste much time struggling to somehow see if PHP could be used, since PHP is my current 'main' lang). Pl/tcl supports dynamic queries - great !. However it introduced it's own little wrinkles. Now for starters I've no knowledge of tcl, however doing my job means learning loads of exteranous crap, and what another lang... I reckon I can do it, just need a little help. So here goes. found out some things too - trigger functions must return opaque (ok not 100% on what opaque is, but I'm not worrying about that just yet), also can't have parameters in the function name - odd, but this lead on to - how the hell would you pass the parameters to the func anyway if it's attached to a trigger - like INSERT - and you merely type the SQL : insert into tablename values bla blabla ; and invoke the trigger, which inturn invokes the function ... erm there - wheres the transport to passing the parameters (sorry i know my programmers lingo isn't 100%). here's my tcl'ed function, which i attached to my main table on INSERT. It's supposed to log the action to another table passed to it - the idea is i've generalised some logging tables to various 'main' tables, and these functions are supposed to record various db and system level events. The function represents a bit of copying and playing - duno what TPL is however it works. I'll address further issues, error checking, processing etc when I've got it actually doing something : create function update_trans () returns opaque as ' spi_exec -array TPL "insert into $1 (objid, objtbl, et, event, time, reason, owner) values (\'$2\', \'$3\', \'$4\', \'$5\', now(), \'$6\', \'$7\')" ' language 'pltcl' ; which works !! well, gets invoked however it doesn't know what 'text' is. Not sure where that got picked up from, but it obviously didn't work - however the insert did. Now I found out there's a parameter array tgargs or something, but how does this get set ? How does it then get accessed in the function ? OK, being the resourceful chap I am (relatively) how about this, a slight diversion. Why not just make up some function which do the main insert, business logic, event logging stuff explicity and sack off doing sql inserts/update etcs. (can't return opaque here, but that's no bother, i think (hope)) New function create function update_trans (text, int4, text, text, text, text, text) returns boolean as ' spi_exec -array TPL "insert into $1 (objid, objtbl, et, event, time, reason, owner) values (\'$2\', \'$3\', \'$4\', \'$5\', now(), \'$6\', \'$7\')" return1 ' language 'pltcl' ; then call these from my code like select update_trans (bla, 1, bla, bla blabl) ; which works also. I get to pass all the parameters i want, and have full control over execution. OK this looses part of the reason for doing this in the first place - tracking people who side track the app code by modifying the db directly, however since noone should be doing that anyway, no problem. (note should). Again apologies for the verbose message - i feel the 'fuller picture' is more useful in the long run, rather than diconnected questions. Well it is to me when I'm on your side of the fence. Thanks for the reply BTW. Oh, and why is this news group only accessible during late afternoon from 3pm'ish GMT. I'm access it from the UK. All morning, for two days, I couldn't get on - server busy errors. Oh and before I forget - over several months of news group postings there has been the recognision of the need for examples for us newies, and some mention of people compiling various docs for just such a purpose - anyone get anywhere with any of these, as they were several months ago. I'm certainly gaining some real gotcha type info on all of this :) Now I've had it. Burned out. So off to the pub and henceforth become as drunk as a skunk ! Regards Rob
[SQL] Wierd postgres Problem
Hi All, I am trying to define a new set of tables an I am getting this strange syntex problem on date, or timestamp data types. I am also getting error on not null constranit as well... Postgres is behaving strangely first definations with not null cmdb=# create table media_received ( cmdb(# comp_id not null, cmdb(# dept_id not null, cmdb(# date_rec timestamp default 'now', cmdb(# units int4 default 0, cmdb(# media_type varchar(64), cmdb(# enqued int4 check (enqued=
Re: [SQL] parse error in create index
Hubert Palme <[EMAIL PROTECTED]> writes: > adressen=> CREATE INDEX xxx ON geburtstage (geb_monat(geburtstag)); > ERROR: DefineIndex: (null) class not found > adressen=> Apparently you're using 6.5 or older ... I'd recommend updating! IIRC, in <= 6.5 you *must* specify an operator class for a functional index. So, CREATE INDEX xxx ON geburtstage (geb_monat(geburtstag) float8_ops); (assuming that geb_monat returns a float8, else adjust to suit). Don't have a 6.5 server running anymore to check this on, however. regards, tom lane
Re: [SQL] view does not show all records it should
Giovanni Biscuolo <[EMAIL PROTECTED]> writes: > CREATE VIEW mag_scaricati_view AS > SELECT s.id_carico, SUM(s.qta_scaricata) AS Scaricati > FROM mag_scarico_tbl s > GROUP BY s.id_carico; Grouped views don't work very well in versions before 7.1, though I'm not sure if that is the issue here or not. (What PG version are you using, anyway?) > Now, when I issue the following command: > SELECT * FROM mag_giacenza1_view > it shows me the records that was in the database the day > I created the view, _not all the records in the database_. > If I select "by hand" with the following statament (please > note it is the very same with which I created the view): > it shows me all the records. That's fairly hard to believe, unless you ran the query using the view inside a transaction that had been open the whole time. I suspect that you have misinterpreted the behavior, and that the real problem is not related to when the records were inserted. Can you show us exactly what you're getting from each query? regards, tom lane
Re: [SQL] view does not show all records it should
On Mon, Feb 12, 2001 at 03:54:39PM +0100, Giovanni Biscuolo wrote: > I set up a simple database in wich all works well, exept > one *very* strange (to me) result. > > Some time ago I created this views: > > CREATE VIEW mag_scaricati_view AS > SELECT s.id_carico, SUM(s.qta_scaricata) AS Scaricati > FROM mag_scarico_tbl s > GROUP BY s.id_carico; > > CREATE VIEW mag_giacenza1_view AS > SELECT c.ref AS "Ref.", > p.descrizione_breve AS Descrizione, > c.id_carico AS "Scheda di carico", > c.qta_caricata AS "Caricati", > s.Scaricati, > (c.qta_caricata-s.Scaricati) AS "Giacenza" > FROM mag_carico_tbl c, mag_scaricati_view s , prd_ref_tbl p > WHERE (c.id_carico = s.id_carico) AND > (c.ref = p.ref); > > Now, when I issue the following command: > > SELECT * FROM mag_giacenza1_view > > it shows me the records that was in the database the day > I created the view, _not all the records in the database_. > If I select "by hand" with the following statament (please > note it is the very same with which I created the view): > Are you absolutely sure you did "CREATE VIEW" and not "CREATE TABLE" ? PostgreSQL allows you to create a table by selecting from existing tables. The sytax _exactly_ matches the create view syntax, except for the single word TABLE vs. VIEW. You can test this from pgsql with: \d mag_giacenza1_view If it's a view, you'll see the column definitions, plus the parsed version of the SELECT you used to create it. If it's a table, you'll just see the column defines. Which version of PostgreSQL are you using, BTW? Ross
Re: [SQL] Wierd postgres Problem
Najm Hashmi writes: > cmdb=# create table media_received ( > cmdb(# comp_id not null, > cmdb(# dept_id not null, ^ Those two fields should have a data type. Same in your other examples. > cmdb(# date_rec timestamp default 'now', > cmdb(# units int4 default 0, > cmdb(# media_type varchar(64), > cmdb(# enqued int4 check (enqued= cmdb(# encoded int4 check(encoded= cmdb(# insys int4 check(insys= cmdb(# constraint media_rec_pk primary key(comp_id,dept_id,date_rec), > cmdb(# constraint media_dept_fk foreign key(dept_id) references department, > cmdb(# constraint media_comp_fk foreign key(comp_id) references company > cmdb(# ); > ERROR: parser: parse error at or near "not" -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
RE: [SQL] plpgsql grief
Title: RE: [SQL] plpgsql grief
Hi, Rob,
From the conversation taht Tom Lane got involved with earlier after my last posting on this toping I think that you need to change your first function to this:
create function testfunc (text) returns int4 as '
declare
sql varchar;
begin
sql=''SELECT id AS res2 FROM ''||$1 ;
execute sql ;
return res2;
end;
' language 'plpgsql' ;
Please note the AS syntax rather than INTO. This was a misconception on my part, which has obviously lasted longer than I'd hoped ;-)
create function update_trans (text, integer, text, text, text, text,
text) returns boolean as '
declare
sql varchar;
tbl alias for $1 ;
begin
sql = ''insert into ''||$tbl||''(objid, objtbl, et, event, time, reason,
owner) values (''||$2||'', ''||$3||'', ''||$4||'', ''||$5||'', now(), ''||$6||'', ''||$7||'')'';
execute sql;
return 0;
end;
' language 'plpgsql' ;
You're forgetting that you have to unquote your variables, because you are constructing a string, and then executing that. Using a varchar called SQL is a good habit for debugging. It separates constructing the string from executing the query.
Cheers...
MikeA
-Original Message-
From: rob [mailto:[EMAIL PROTECTED]]
Sent: 08 February 2001 16:48
To: [EMAIL PROTECTED]
Subject: [SQL] plpgsql grief
Hi, I'm having some real headache problems here. Apologies for the
length, i just want to get it all out now :)
I figured moving some 'simple' db code from my application to it's more
natural home in the db would work out. Bummer. Not only do i have to run
7.1 (beta 4) to be able to dynamically generate queries, I'm finding it
*extrememly* difficult to get to get my simple functions to work (plus
for the 'widest used open source db' i'm finding examples very hard to
come by)
Before I start if anyone has any pointers to coding examples (and I mean
a little more than the standard postgres docs :) I'd be eternally
greatful. Failing that, can anyone help with these two simple (ahem)
codelets :
Example 1 :
create function testfunc (text) returns int4 as '
declare
sql varchar;
res int4;
begin
sql=''SELECT INTO res2 id FROM ''||$1 ;
execute sql ;
return res;
end;
' language 'plpgsql' ;
simple function to return the id field of a table (passed to the
function). ok, not a real world example, however i do this :
#select testfunc('tablenam') ;
and i get
ERROR: parser: parse error at or near "into"
ok this is actually first things last. I'm not really bothered about
returing values into local variables and then returning them, it's just
a run through. If I can't get this right, what chance have i got at
sorting out the real work i want to do.
Example 2 :
create function update_trans (text, integer, text, text, text, text,
text) returns boolean as '
declare
tbl alias for $1 ;
begin
execute ''insert into tbl (objid, objtbl, et, event, time, reason,
owner) values ($2, $3, $4, $5, now(), $6, $7)'';
return 0;
end;
' language 'plpgsql' ;
# select update_trans('tablname','1'
,'sometext','sometext','sometext','sometext','sometext') ;
ERROR: Relation 'tbl' does not exist
dur. yeah i know it doesn't exist cause i want to pass it in parameter
1. Tried substituting tbl with $1 and quote_ident($1) and
quote_ident(tbl) in the sql string, but that didn't work either. (BTW
anyone know of any GUI interfaces that support 7.1 - phpPgAdmin 2.1,
2.2.1 and 2.3 seem to balk on functions)
Example 2 is prelude to a larger function (not much larger - but then
this is relavitve to how easy to code it is) to monitor the changes made
by a user, what they change from and to and who/when/why this is
already implemented in my app code - PHP - and checking out the features
available in postgres i figured i could do some kind of looping through
the OLD and NEW dataset-array things, comparing them against each other,
sorta like this :
for ($i = 0 ; $i < count($NEW) ; $i++) {
/* since $NEW and $OLD are essentially the same we can do this */
if ($OLD[$i] != $NEW[$i])
record the change bla bla bla
}
I'm really hoping I can, as at this rate I've spent the better part of
three days trying to figure the simple things above out and the only
thing i'm about to reach is breaking point...
Sorry for the sarcasm, I'm about to pop.
Rob
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
Re: [SQL] Wierd postgres Problem
Your missing your fields types, i.e.: CREATE TABLE media_received ( comp_id SERIAL NOT NULL, dept_id INT4NOT NULL, date_recTIMESTAMP DEFAULT 'now', that should fix your problem... Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 13 Feb 2001, Najm Hashmi wrote: > Hi All, > I am trying to define a new set of tables an I am getting this strange > syntex problem on date, or timestamp data types. I am also getting error on > not null constranit as well... Postgres is behaving strangely first > definations with not null > > cmdb=# create table media_received ( > cmdb(# comp_id not null, > cmdb(# dept_id not null, > cmdb(# date_rec timestamp default 'now', > cmdb(# units int4 default 0, > cmdb(# media_type varchar(64), > cmdb(# enqued int4 check (enqued= cmdb(# encoded int4 check(encoded= cmdb(# insys int4 check(insys= cmdb(# constraint media_rec_pk primary key(comp_id,dept_id,date_rec), > cmdb(# constraint media_dept_fk foreign key(dept_id) references department, > cmdb(# constraint media_comp_fk foreign key(comp_id) references company > cmdb(# ); > ERROR: parser: parse error at or near "not" > > Second definition without not null and data type timestamp, > cmdb=# create table media_received ( > cmdb(# comp_id, > cmdb(# dept_id, > cmdb(# date_rec timestamp, > cmdb(# units int4 default 0, > cmdb(# media_type varchar(64), > cmdb(# enqued int4 check (enqued= cmdb(# encoded int4 check(encoded= cmdb(# insys int4 check(insys= cmdb(# constraint media_rec_pk primary key(comp_id,dept_id,date_rec), > cmdb(# constraint media_dept_fk foreign key(dept_id) references department, > cmdb(# constraint media_comp_fk foreign key(comp_id) references company > cmdb(# ); > ERROR: parser: parse error at or near "timestamp" > 3rd def with date as data type: > cmdb=# create table media_received ( > cmdb(# comp_id, > cmdb(# dept_id, > cmdb(# date_rec date, > cmdb(# units int4 default 0, > cmdb(# media_type varchar(64), > cmdb(# enqued int4 check (enqued= cmdb(# encoded int4 check(encoded= cmdb(# insys int4 check(insys= cmdb(# constraint media_rec_pk primary key(comp_id,dept_id,date_rec), > cmdb(# constraint media_dept_fk foreign key(dept_id) references department, > cmdb(# constraint media_comp_fk foreign key(comp_id) references company > cmdb(# ); > ERROR: parser: parse error at or near "date" > > is something wrong with my table definition syntex? > All kind of help is appreciated. > Regards, Najm > >
Re: [SQL] plpgsql grief
Rob,
> I figured moving some 'simple' db code from my
> application to it's more
> natural home in the db would work out. Bummer. Not only
> do i have to run
> 7.1 (beta 4) to be able to dynamically generate queries,
> I'm finding it
> *extrememly* difficult to get to get my simple functions
> to work (plus
> for the 'widest used open source db' i'm finding examples
> very hard to
> come by)
Keep in mind that Open Source usually means DIY as well, or
it wouldn't be free. If you have mission-critical problems,
pay-for support is available from two companies.
As for the PL/pgSQL documentation, everyone acknowledges
it's skimpy at best. Several of us PL/pgSQL users plan to
write up more extensive docs *when we have time*. The doc
writers will be volunteers, so don't hold your breath.
And, as another developer pointed out, the EXECUTE
functionality already goes beyond the scope of Microsoft's
Transact SQL, a $1000=$10,000 + product.
In the meantime:
> Example 1 :
>
> create function testfunc (text) returns int4 as '
> declare
> sql varchar;
> res int4;
> begin
> sql=''SELECT INTO res2 id FROM ''||$1 ;
> execute sql ;
> return res;
> end;
> ' language 'plpgsql' ;
SELECT INTO functionality is being dropped from EXECUTE. If
you're interested in the reasons why, we've been discussing
it on the list for the last 2 weeks; leaf throud the
archives.
The main restriction is this: EXECUTE passes the query to a
seperate sub-process, and as such you may not pass *any*
unexpanded variables into the EXECUTE statement. Within
EXECUTE, those variables are out of scope.
Thus your only way to get stuff back from EXECUTE is to save
the results you want to a temporary table (using CREATE
TABLE AS ...), and read them back using a query. Not
high-performance, but it gets the job done.
Tom Lane and Jan Wieck have suggested that we might have
more flexible dynamic query generation for 7.2, but that's a
ways off.
Thus, your second function should be:
> create function update_trans (text, integer, text, text,
> text, text,
> text) returns boolean as '
> declare
> tbl alias for $1 ;
> begin
> execute ''insert into tbl (objid, objtbl, et, event,
> time, reason,
> owner) values ('' || $2 || '', '' || $3 || '', '' || $4
|| '', '' || $5 || '', current_timestamp, '' || $6 || '',
'' || $7 || '')'';
> return TRUE;
> end;
> ' language 'plpgsql' ;
With adjustments made to the syntax for data type delimiters
and replacing any nulls with the work NULL (and keep in mind
that Postgres functions currently have trouble with NULLS as
input parameters).
The rest is up to you ... or hire an expert.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
Re: [SQL] plpgsql grief
> Thus your only way to get stuff back from EXECUTE is to save > the results you want to a temporary table (using CREATE > TABLE AS ...), and read them back using a query. Not > high-performance, but it gets the job done. > I believe this statement is incorrect, quoting Michael Ansley <[EMAIL PROTECTED]> from a previous e-mail: create function testfunc (text) returns int4 as ' declare sql varchar; begin sql=''SELECT id AS res2 FROM ''||$1 ; execute sql ; return res2; end; ' language 'plpgsql' ; Please note the AS syntax rather than INTO. This was a misconception on my part, which has obviously lasted longer than I'd hoped ;-) I believe this was the consensus reached on the hacker's list. Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio
Re: [SQL] plpgsql grief
"Josh Berkus" <[EMAIL PROTECTED]> writes:
> Thus your only way to get stuff back from EXECUTE is to save
> the results you want to a temporary table (using CREATE
> TABLE AS ...), and read them back using a query. Not
> high-performance, but it gets the job done.
That's not the only way; you can also use FOR ... EXECUTE, as Jan
pointed out awhile back in the other thread.
What does not work at the moment is to EXECUTE a 'SELECT INTO',
because EXECUTE just hands the string off to the main SQL parser
which knows nothing about plpgsql variables. We'll try to improve
this for 7.2, but it's far too late to get it done for 7.1.
> Thus, your second function should be:
>> create function update_trans (text, integer, text, text,
>> text, text,
>> text) returns boolean as '
>> declare
>> tbl alias for $1 ;
>> begin
>> execute ''insert into tbl (objid, objtbl, et, event,
>> time, reason,
>> owner) values ('' || $2 || '', '' || $3 || '', '' || $4
> || '', '' || $5 || '', current_timestamp, '' || $6 || '',
> '' || $7 || '')'';
>> return TRUE;
>> end;
>> ' language 'plpgsql' ;
> With adjustments made to the syntax for data type delimiters
> and replacing any nulls with the work NULL.
Hm, good point; coping with NULLs in this context will require some
explicit programming. Yech. I'd recommend using quote_string for
the TEXT parameters, but that doesn't help any for the NULL case.
(I wonder if quote_string should be defined to return 'NULL' for
a NULL input? Again, too late for 7.1, but seems like a good future
improvement.)
> (and keep in mind
> that Postgres functions currently have trouble with NULLS as
> input parameters).
Not in 7.1 they don't ...
regards, tom lane
Re: [SQL] plpgsql grief
Michael Ansley <[EMAIL PROTECTED]> writes: > create function testfunc (text) returns int4 as ' > declare > sql varchar; > begin > sql=''SELECT id AS res2 FROM ''||$1 ; > execute sql ; > return res2; > end; > ' language 'plpgsql' ; > Please note the AS syntax rather than INTO. That won't work :-( regards, tom lane
Re: [SQL] plpgsql grief
Rob, >Just need the info - i can do the rest. I also, due to my > business requirements, need to do this as quickly as possible - maybe > not the expert you perhaps are No expert at all, according to Tom Lane. > As it goes I've implemented most of what I wanted in pl/tcl (having > learned tcl today). That's all i need, and it makes me most happy :). That's great! If I need to hire a pl/tcl expert, I now know who to e-mail ... While you're at it, how about writing a 2-5 page pl/tcl HOWTO to help others who follow in your (frustrated) footsteps? > Just thought the built in procedure implmentation would be quite mature > by now. Implementation is in its adolescence/young adulthood. Documentation is still in its early childhood. We (the user base) will fix that, but not this month. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
RE: [SQL] plpgsql grief
Title: RE: [SQL] plpgsql grief I thought that the discussion on this topic resolved that the AS syntax would work as I described, and the INTO syntax would be removed because of ambiguity, to be redeveloped at a later date? -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 12 February 2001 16:46 To: Michael Ansley Cc: 'rob'; [EMAIL PROTECTED] Subject: Re: [SQL] plpgsql grief Michael Ansley <[EMAIL PROTECTED]> writes: > create function testfunc (text) returns int4 as ' > declare > sql varchar; > begin > sql=''SELECT id AS res2 FROM ''||$1 ; > execute sql ; > return res2; > end; > ' language 'plpgsql' ; > Please note the AS syntax rather than INTO. That won't work :-( regards, tom lane ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
Re: [SQL] plpgsql grief
Tom, > > (and keep in mind > > that Postgres functions currently have trouble with NULLS as > > input parameters). > > Not in 7.1 they don't ... Really? Terrific. Sadly, I have 25+ PL/pgSQL functions not set up to accept NULLs ... Can you point me to (or tell me where to search) the developer notes on what improvements have been made in the function parser so that I can take advantage of them? -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Re: [SQL] plpgsql grief
Michael, > I thought that the discussion on this topic resolved that the AS > syntax would work as I described, and the INTO syntax would be removed > because of ambiguity, to be redeveloped at a later date? * Tom (I believe) was referring to CREATE TABLE AS as a way to insert queries into temporary tables. For better results, check out Jan's syntax for FOR ... EXECUTE in his last post. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Re: [SQL] plpgsql grief
Michael Ansley <[EMAIL PROTECTED]> writes: > I thought that the discussion on this topic resolved that the AS syntax > would work as I described, and the INTO syntax would be removed because of > ambiguity, to be redeveloped at a later date? INTO has indeed been removed. However, AS does not do what you seem to think it does; in fact, it is entirely a noise clause in this context. regards, tom lane
[SQL] postgres's users take on onlamp
heya:
just wondering if anyone has any comments on this
onlamp is o'rielly's new ideal
that ,really, has been in use for quite a while
its anacronym stands for
linux apache mysql and {php/perl/python}
more info here:
http://www.onlamp.com/
[SQL] combining
I'll really appreciate help on this if anyone will do so. I'm used to single-table stuff, but not sure the best way to do things with multiple tables, but here goes: Given two tables with a common "id" field, first table columns: id lastname Second table columns: id type content These two queries get me what I want from each table: select unique id from table1 where lastname='morton'; select unique id from table2 where ((type = "pie") and (content = 'apple)); What is the best way to combine these into one query? Thanks. Frank
Re: [SQL] combining
Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Mon, 12 Feb 2001, Frank Morton wrote: > I'll really appreciate help on this if anyone will do so. I'm > used to single-table stuff, but not sure the best way to > do things with multiple tables, but here goes: > > Given two tables with a common "id" field, first table columns: > > id > lastname > > Second table columns: > > id > type > content > > These two queries get me what I want from each table: > > select unique id from table1 where lastname='morton'; > select unique id from table2 where ((type = "pie") and (content = 'apple)); it depends what you want: 1. select id from table1 where lastname='morton' and id=table2.id and table2.type = 'pie' and table2.content ='apple'; will return you the id in the intersection of 2 sets. 2. select id from table1 where lastname='morton' union select id from table2 where ((type = 'pie') and (content = 'apple')); will return you a set1+set2; > > What is the best way to combine these into one query? > > Thanks. > > Frank > >
Re: [SQL] combining
Frank Morton writes: > These two queries get me what I want from each table: > > select unique id from table1 where lastname='morton'; > select unique id from table2 where ((type = "pie") and (content = 'apple)); > > What is the best way to combine these into one query? select table1.id from table1, table2 where table1.id = table2.id and lastname = 'morton' and type='pie' and content = 'apple'; Or in 7.1, optionally: select id from table1 natural join table2 where lastname = 'morton' and type ='pie' and content = 'apple'; -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] Recusrive Functions in 7.0.3
If I remove the line calling PERFORM it works without problems moving the children node for the passed integer into test and then exiting. I can then repeat the process going through the output to test iteratively and do not have any problems. However if I put the PERFORM line back in to create a recursive function it just goes on forever, and I only have 6 nodes. CREATE FUNCTION get_children (integer) RETURNS integer AS ' DECLARE pnode_parent ALIAS FOR $1; rec RECORD; BEGIN FOR rec IN SELECT * FROM tree_adjacency_matrix WHERE node_parent = pnode_parent LOOP INSERT INTO test (node1, node2) VALUES(rec.node_child, rec.node_parent); PERFORM get_children(rec.node_child); END LOOP; RETURN 0; END; 'LANGUAGE 'plpgsql' Mark On Saturday 10 February 2001 03:29, Tom Lane wrote: > <[EMAIL PROTECTED]> writes: > > Are recursive functions allowed in in 7.0.3 > > Sure. > > play=> create function myfactorial(int) returns int as ' > play'> begin > play'> if $1 > 1 then > play'> return $1 * myfactorial($1 - 1); > play'> end if; > play'> return $1; > play'> end;' language 'plpgsql'; > CREATE > play=> select myfactorial(1); > myfactorial > - >1 > (1 row) > > play=> select myfactorial(10); > myfactorial > - > 3628800 > (1 row) > > play=> > > I get a stack overflow crash at about myfactorial(7500), but that seems > like a sufficient level of recursion depth for normal purposes ... > > > as I seem to be unable to > > get them to work in plpgsql, > > Are you sure you aren't asking for infinite recursion, eg by invoking > the same function with the same argument? > > regards, tom lane
[SQL] Re: Recusrive Functions in 7.0.3
Ahh I found what I was doing wrong, there was a rogue value being returned causing to infinite loop. Its fixed now. Creating that script you recommended set my thinking process straight. many thanks Mark On Saturday 10 February 2001 08:42, Tom Lane wrote: > mark proctor <[EMAIL PROTECTED]> writes: > > However if I put the PERFORM line back in to create a recursive > > function it just goes on forever, and I only have 6 nodes. > > Hm. There may be a bug here, or maybe you're still confused... but I'm > not eager to reverse-engineer your table declarations and data from this > sketch. Would you mind providing a complete example, ie a SQL script to > reproduce the problem starting from an empty database? > > regards, tom lane
Re: [SQL] What's wrong with this function
the select query returns the first row to rec. You can then access its values with: rec.field_name at END LOOP it jumps back to FOR checks to see if there any more rows and if so moves to the next row and repeats the loop. It also looks like your missing a LOOP keyword at the end of the FOR line. Here is an example that works. CREATE FUNCTION get_children (integer) RETURNS integer AS ' DECLARE pnode_parent ALIAS FOR $1; rec RECORD; BEGIN FOR rec IN SELECT * FROM tree_adjacency_matrix WHERE node_parent = pnode_parent LOOP INSERT INTO test (node1, node2) VALUES(stm.node_child, .rec.node_parent); END LOOP; RETURN 0; END; 'LANGUAGE 'plpgsql' Mark On Saturday 10 February 2001 20:23, Najm Hashmi wrote: > Jie Liang wrote: > > I just know you can use implict cursor inside the plpgsql > > e.g > > declare > > result text; > tcount int4; > > > rec record; > > begin > > FOR rec IN select_clause LOOP > > statements > > END LOOP; > > end; > > Thank you Jie for your help. I am bit confused about how it works. I want > for each row , obtained by select statment, get certain values and then do > some calculations and out put that resulst eg > for rec IN select title, dcount from songs where artist='xyz' > tcount:= tcount+rec.dcount; > END LOOP; > return tcount; > would this work ? > Thanks again for your help. > Regards, Najm
[SQL] ORDER BY in SQL functions
Are ORDER BYs allowed in SQL functions? Or do SQL functions work like views that are as unordered as their underlting tables? CREATE FUNCTION foo (INT) RETURNS SETOF INT AS ' SELECT id FROM table WHERE some_colunm > $1 ' LANGUAGE 'sql'; works. But if I try CREATE FUNCTION foo (INT) RETURNS SETOF INT AS ' SELECT id FROM table WHERE some_colunm > $1 ORDER BY some_other_colunm ' LANGUAGE 'sql'; the parser comes back with ERROR: function declared to return int4 returns multiple values in final retrieve The sorting must occur in the function, because once I have a set of IDs, there is no way to order them by some_other_column. The function cannot be rewritten in pl/pgsql because it may return more than one value. -- K. Ari Krupnikov DBDOM - bridging XML and relational databases http://www.iter.co.il
[SQL] Re: plpgsql grief
Tcl is my bread and butter but, coincidentally, I have just started considering pl/tcl 2 days ago as the choice for server side pg programming. I do it in microsoft t-sql right now, and plsql is pretty close to that. However, tcl is like English to me, so I think I will go that way unless someone can tell me why I shouldn't. I found that the docs are actually pretty excellent, although brief, on pl/tcl. Tcl is a strange animal, so you will have to get used to 'lists' and occasional unwanted/unexpected variable substitution until you get the hang of it. But if you scan the docs you will find some interesting stuff. It seems they have provided everything you and I will ever need, we just need to figure out how to use it. The docs are in USER versus PROGRAMMER. Go figure. There is a newsgroup called 'novice' that is useful from time to time. I don't know why the server has had such availability issues the last couple days, it is usually very good. Have fun, Ian rob wrote: > > > > > I feel your pain;^) > > > > Here is the text of a post from Tuesday... I think it answers your question > > which is that you cannot do variable subsititution for table or field names > > inside procedures. This is not a Postgres specific limitation, MS SQL > > Server has the same issue. > > > > > > "DR" == David Richter > > > writes: > > Ya know, i already read this one. That's what got me on to 7.1 and using > EXECUTE :) > > ARgh! Apparenty I've opened up a real can of worms with wanting cool > general functions, with a bit of dynamism and business logic. > However > > OK dumped pl/pgsql, thinking pl/tcl is more my bag (didn't fancy > recompiling perl to get the shared lib, and didn't want to waste much > time struggling to somehow see if PHP could be used, since PHP is my > current 'main' lang). Pl/tcl supports dynamic queries - great !. However > it introduced it's own little wrinkles. Now for starters I've no > knowledge of tcl, however doing my job means learning loads of > exteranous crap, and what another lang... I reckon I can do it, just > need a little help. So here goes. > > found out some things too - trigger functions must return opaque (ok not > 100% on what opaque is, but I'm not worrying about that just yet), also > can't have parameters in the function name - odd, but this lead on to - > how the hell would you pass the parameters to the func anyway if it's > attached to a trigger - like INSERT - and you merely type the SQL : > > insert into tablename values bla blabla ; > > and invoke the trigger, which inturn invokes the function ... erm there > - wheres the transport to passing the parameters (sorry i know my > programmers lingo isn't 100%). > > here's my tcl'ed function, which i attached to my main table on INSERT. > It's supposed to log the action to another table passed to it - the idea > is i've generalised some logging tables to various 'main' tables, and > these functions are supposed to record various db and system level > events. The function represents a bit of copying and playing - duno what > TPL is however it works. I'll address further issues, error checking, > processing etc when I've got it actually doing something : > > create function update_trans () returns opaque as ' > spi_exec -array TPL "insert into $1 (objid, objtbl, et, event, time, > reason, owner) values (\'$2\', \'$3\', \'$4\', \'$5\', now(), \'$6\', > \'$7\')" > ' language 'pltcl' ; > > which works !! well, gets invoked however it doesn't know what 'text' > is. Not sure where that got picked up from, but it obviously didn't work > - however the insert did. Now I found out there's a parameter array > tgargs or something, but how does this get set ? How does it then get > accessed in the function ? > > OK, being the resourceful chap I am (relatively) how about this, a > slight diversion. Why not just make up some function which do the main > insert, business logic, event logging stuff explicity and sack off doing > sql inserts/update etcs. > > (can't return opaque here, but that's no bother, i think (hope)) > > New function > > create function update_trans (text, int4, text, text, text, text, text) > returns boolean as ' > spi_exec -array TPL "insert into $1 (objid, objtbl, et, event, time, > reason, owner) values (\'$2\', \'$3\', \'$4\', \'$5\', now(), \'$6\', > \'$7\')" > return1 > ' language 'pltcl' ; > > then call these from my code like > > select update_trans (bla, 1, bla, bla blabl) ; > > which works also. I get to pass all the parameters i want, and have full > control over execution. OK this looses part of the reason for doing this > in the first place - tracking people who side track the app code by > modifying the db directly, however since noone should be doing that > anyway, no problem. (note should). > > Again apologies for the verbose message - i feel the 'fuller picture' is > more useful in the long run, rather than diconnected questions. Well it > is to me when I'm on your side of the f
Re: [SQL] ORDER BY in SQL functions
"K. Ari Krupnikov" <[EMAIL PROTECTED]> writes: > CREATE FUNCTION foo (INT) > RETURNS SETOF INT AS ' > SELECT id > FROM table > WHERE some_colunm > $1 > ORDER BY some_other_colunm > ' LANGUAGE 'sql'; > ERROR: function declared to return int4 returns multiple values in > final retrieve This is a bug in the SQL-function support --- the check for correct return type gets confused by the extra hidden column used for the ORDER BY. It'll work if you ORDER BY the column you're returning, not that that helps you much. It's fixed in 7.1. regards, tom lane
Re: [SQL] Wierd postgres Problem
On Tue, Feb 13, 2001 at 10:20:09AM -0500, Najm Hashmi wrote: > Hi All, hi > I am trying to define a new set of tables an I am getting this strange > syntex problem on date, or timestamp data types. I am also getting error on > not null constranit as well... Postgres is behaving strangely first > definations with not null > > cmdb=# create table media_received ( > cmdb(# comp_id not null, > cmdb(# dept_id not null, [...] > is something wrong with my table definition syntex? Yes, you forgot to specify types for comp_id and dept_id fields. greetings, Tom -- .signature: Too many levels of symbolic links
Re: [SQL] PL/PGSQL Cook Book
mark proctor writes: > I've been trawling through the mailist lists and I notice there was > talk back in 1999 abouta PLPGSQL Cook Book - did anything come of > this? If no one is maintaining something like this and people think > its a good idea I think we should have another crack at it. I'd be > happy to maintain something like this and put it up on the web, > although I'm only a newbie and would rely upon user contribution. Start writing, and send it to [EMAIL PROTECTED] It doesn't have to be a great work of literature; incremental work is just fine. >How can I create Tree structures? >Are recursive functions supported? >Whats are the advanteds disadvantes of the different scipting langues PL/PGSQL, >PL/Perl, PL/Tcl? >How do variable scopes work in PL/PGSQL? >Can I output variables from a function to the command line for debugging purposes? >How to debug PL/PGSQL? >Various examples for each of the statements All valid questions. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] PL/PGSQL Cook Book
Even a PL/PgSQL FAQ would be good. > mark proctor writes: > > > I've been trawling through the mailist lists and I notice there was > > talk back in 1999 abouta PLPGSQL Cook Book - did anything come of > > this? If no one is maintaining something like this and people think > > its a good idea I think we should have another crack at it. I'd be > > happy to maintain something like this and put it up on the web, > > although I'm only a newbie and would rely upon user contribution. > > Start writing, and send it to [EMAIL PROTECTED] It doesn't have > to be a great work of literature; incremental work is just fine. > > >How can I create Tree structures? > >Are recursive functions supported? > >Whats are the advanteds disadvantes of the different scipting langues PL/PGSQL, >PL/Perl, PL/Tcl? > >How do variable scopes work in PL/PGSQL? > >Can I output variables from a function to the command line for debugging >purposes? > >How to debug PL/PGSQL? > >Various examples for each of the statements > > All valid questions. > > -- > Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[SQL] pg_dump question
When dumping database, is it possible to dump datetime values without the timezone info? I'm currentlyl using 6.5.
[SQL] [ADMIN] look online connection & user name
thanks
[SQL]how to select * from database1 table,database2 table
if join database1 database2how to makeI use VB or DELPHIthanks
[SQL] My apologies...
My apologies to the lists...in trying to stop my own spam, I spammed you all. This has now been fixed. Again, sorry for any inconvenience. -Ken
Re: [SQL] postgres's users take on onlamp
Well, maybe if Postgres' name was Mostgres, the "M"
would stand for Mostgres instead of MySQL ...
V.Paul
clayton cottingham wrote:
>
> heya:
> just wondering if anyone has any comments on this
>
> onlamp is o'rielly's new ideal
> that ,really, has been in use for quite a while
>
> its anacronym stands for
> linux apache mysql and {php/perl/python}
>
> more info here:
>
> http://www.onlamp.com/
