[SQL] Problem with pg_index.
Hi the list ! I'm new to this list. I've tried to write a sql query to get all the fields of a table and to have mentionned is a field can be null or not, and if a field is a key or not. Before showing the query I send, let me tell you that I'm running postgre v7.1.2 on a SuSE Linux 6.4 box, and that everything works fine but this query. Here it is : select pg_class.oid, pg_attribute.attname, pg_attribute.attbyval, pg_attribute.attnotnull from pg_class, pg_attribute, pg_index where relname='essai2' and pg_attribute.attrelid=pg_class.oid and pg_attribute.attnum >0 and pg_index.indrelid=pg_class.oid and pg_index.indkey[0] = pg_attribute.attnum This query doesn't work, the back-end answers Error : Invalid Command name "0"... What now ? I've checked out the online doc, and the archives of this list (that's where I got pg_index.indkey[0] from), but nothing seems to work, since pg_index.indkey seems to be an int2vector, and I found no integrated function to check if a value is in this vector... Thanks for your ideas and//or help. -- David BOURIAUD -- In a world without walls or fences, what use do we have for windows or gates ? -- ICQ#102562021 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problem with pg_index.
David BOURIAUD <[EMAIL PROTECTED]> writes: > This query doesn't work, the back-end answers Error : Invalid Command > name "0"... I don't think so: there is no such error string anywhere in the PG sources. (Also, when I try the query, it seems to work fine.) However, I find the following possibly relevant match in the PGAccess FAQ: 8. I am receiving the following error: message invalid command name "namespace" while executing "namespace eval Mainlib ..." That means 100% that you have an older version of Tcl/Tk that don't recognize namespaces command. Please upgrade to Tcl/Tk 8.0.x minimum Are you using Tcl? If so, I'd bet the problem is on the client side. regards, tom lane ---(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
[Fwd: [SQL] Problem with pg_index.]
-- David BOURIAUD -- In a world without walls or fences, what use do we have for windows or gates ? -- ICQ#102562021 Tom Lane wrote: You are totally right ! > > I don't think so: there is no such error string anywhere in the PG > sources. (Also, when I try the query, it seems to work fine.) > > However, I find the following possibly relevant match in the PGAccess FAQ: > > 8. I am receiving the following error: message invalid command > name "namespace" while executing "namespace eval Mainlib ..." > That means 100% that you have an older version of Tcl/Tk that > don't recognize namespaces command. Please upgrade to Tcl/Tk 8.0.x >minimum > > Are you using Tcl? If so, I'd bet the problem is on the client side. Indeed I use pgaccess, and when I type the query under psql, it works fine, so I'm downloading the sources of tcl8.3.3 and upgrade as soon as possible. Yet, I have a problem with complex keys, I can't check pg_index[0] on every row fetched, so, how can I do ? I've tried to add arrays extentions, but the *= operator doesn't work with int2vector How can I do then ? Thanks anyway ! Have a nice week-end. -- David BOURIAUD -- In a world without walls or fences, what use do we have for windows or gates ? -- ICQ#102562021 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Date manipulation
Mark,
> How does one perform date manipulation within SQL? For example, SQL
> Server has a dateadd() function that takes a date part, scalar, and
> the
> date to manipulate.
As I have remarked before, such functions as DATEADD are unnecessary in
PostgreSQL because PostgreSQL has a proper implementation of Date data
types, unlike MS SQL Server. Thus, to add to a date:
new_date := old_date + INTERVAL('1 week');
Or to subtract:
break_time := restart_time - stop_time;
It's improtant to remeber that the differnence of two dates or times is
an interval, and while you can add an interval to a date you cannot add
two dates.
Additionally, if you browse to Roberto Mello's PG/plSQL function library
(see link for the PostgreSQL.org web site) you will find an extension to
the OVERLAPS function that I find quite useful (I should, I wrote it!).
-Josh
__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
---(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] question about PL/pgSQL function
Jack, > select into rec1 * from EMP where empNo =''''; > i1 := xxx(rec1,5); Bad syntax. The correct syntax is: SELECT * INTO rec1 FROM EMP WHERE empNo=''''; > 2. Is that possible to return a record from a function? And how do I > get the > returned record in PL/pgSql No, it is not possible. I know the list archives are kinda hard to search, but we've discussed this question ad naseum; in fact, I just provided a workaround earlier this week. -Josh __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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] PGAccess/pgplsql Blues
Folks, I've been having an annoying problem that I'm hoping someone else has had and solved: When I edit some of my more complex plpgsql functions using PGAccess, the functions break and cannot be made to work again except by reloading them from PLSQL/text. All attempts to use the function after PGAccess editing are met with: 'Parse error at or near ""' Once the *identical* function text is reloaded via PSQL command-line, however, the function works. I'm using Postgres 7.1 RC2 and PGAccess 0.98.5. -Josh __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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] PGAccess/pgplsql Blues
"Josh Berkus" <[EMAIL PROTECTED]> writes: > When I edit some of my more complex plpgsql functions using PGAccess, > the functions break and cannot be made to work again except by reloading > them from PLSQL/text. All attempts to use the function after PGAccess > editing are met with: 'Parse error at or near ""' Hmm, is it possible PGAccess is DOS-ifying the newlines? At one point I fixed plpgsql to accept DOS-style newlines, but maybe someone broke it again. Or maybe that's not the problem, but it's worth checking. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PGAccess/pgplsql Blues
On Fri, Jun 01, 2001 at 09:30:40AM -0700, Josh Berkus wrote: > > When I edit some of my more complex plpgsql functions using PGAccess, > the functions break and cannot be made to work again except by reloading > them from PLSQL/text. All attempts to use the function after PGAccess > editing are met with: 'Parse error at or near ""' IIRC, pgaccess does quote-escaping for you, so if you try to write "standard" PL/pgSQL (escaping single quotes), it'll barf this error. Just something to check. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer I'm only a droid and not very knowledgeable about such things. ---(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
