Re: [SQL] Foreign Key between different databases
AFAIK, not easily. There was a post to the hackers list recently with code for a "database connector" function. Using this it may be possible to write your own referential integrity triggers that reference the other database.. maybe not. I didn't look much into the code. There might be something else around somewhere. Note that in order to enforce "full" RI you will have to write these functions in both databases. Oh, and the other database does have to be a Postgresql database with the above mentioned code. -Cedar On Tue, 10 Apr 2001, Stan van de Mortel wrote: > Hello, > > I've a table in a database and I want to create a column that is a foreign > key to a table in an other database. When I try ...,foreign key (x) > references otherdb.table(x) it says 'parse error at or near "."'. > > Is there an other way to create this kind of foreign key? > > Stan van de Mortel > [[EMAIL PROTECTED]] > > > ---(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 > ---(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] problem with copy command
Jaruwan Laongmal wrote: > dear all, > I currently using postgresql v7.0.3 > when i import text file to table with command "copy tablename from > '/tmp/a.txt'; > and it shows > "copy: line 20, Cannot insert a duplicate key into unique index testpri_pk" > ,then it exits with doing nothing. > > I want to ignore this errors and continue copy the next record. How to do > that? > if I don't filter in '/tmp/a.txt' before using copy command. > > Thank you so much for your help in advance . > Regards > Jaruwan Try to delete the unique index testpri_pk ... but if you want to create the unique index again you must delete (or modify) you'r not_unique rows. George Moga, Data Systems Srl Slobozia, ROMANIA ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Concatenate fields
Hi all, using PgSQL 7.0.3 I would like concatenate some fields to have all information in just one field. I'm doing this below: CREATE TABLE bill_2col AS SELECT bill.bill_id, (trim(text(bill.bill_number)) || ' | ' || trim(text(provider.company)) || ' | ' || trim(to_char(bill.issue_date,'MM/DD/YY')) || ' | ' || trim(to_char(bill.amount,'999.99')) || ' pts') AS billdesc FROM bill, provider WHERE bill.provider_id = provider.provider_id ORDER BY bill.bill_id; When it finds some empty field, it makes all the new field empty, no matters if the other are empty or not. Here you have the result table bill_id | billdesc -+--- 0 | Unknown | Unknown | 01/01/00 | .00 pts 1 | 98018097 | SUMI Informática | 12/22/99 | 1823520.00 pts 2 | 3 | 99018089 | PISTA CERO S.L | 12/01/99 | 1949380.00 pts 4 | 99018089 | DataSystem Informática | 12/01/99 | 1949380.00 pts 5 | 6 | ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] problem with copy command
Here is a method of filtering out the duplicate keys from the input file: Let us assume that the input data is in /tmp/table.in, that that file is tab-delimited and that the primary key is in field 2. psql -d database -c "COPY table TO '/tmp/table.1'" psql -d database -c "COPY table TO '/tmp/table.2'" cat /tmp/table.in /tmp/table.[12] | sort -k 2 -t \[tab] | uniq -u -W 1 -f 1 -t \[tab] >/tmp/table.in.unique [tab] stands for the sequence "ctrl-V tab", which will force an actual tab character into the command line (the backslash will protect it from the shell). We copy the database out twice to ensure that all keys already in it are excluded, otherwise we would be reintroducing all primary keys that were _not_ in the desired input. The end product is a file that excludes all primary keys that are already in the target table. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Is any one of you in trouble? He should pray. Is anyone happy? Let him sing songs of praise. Is any one of you sick? He should call the elders of the church to pray over him...The prayer of a righteous man is powerful and effective." James 5:13,14,16 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Concatenate fields
Amanda Riera wrote: >I would like concatenate some fields to have all information in just >one field. I'm doing this below: > >CREATE TABLE bill_2col AS >SELECT bill.bill_id, > (trim(text(bill.bill_number)) || ' | ' || > trim(text(provider.company)) || ' | ' || > trim(to_char(bill.issue_date,'MM/DD/YY')) || ' | ' || > trim(to_char(bill.amount,'999.99')) || ' pts') AS billdesc >FROM bill, provider >WHERE bill.provider_id = provider.provider_id >ORDER BY bill.bill_id; > >When it finds some empty field, it makes all the new field empty, no >matters >if the other are empty or not. In this case, empty means NULL. Any concatenation involving NULL returns NULL; this is according to the standard. Use COALESCE(field,'') to return an empty string if field is NULL, so that no NULLs go into the concatenation. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Is any one of you in trouble? He should pray. Is anyone happy? Let him sing songs of praise. Is any one of you sick? He should call the elders of the church to pray over him...The prayer of a righteous man is powerful and effective." James 5:13,14,16 ---(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] enumerating rows
Maybe a trivial question, maybe it's foreign from SQL, I'dont know... How to add a column which stands for the row number in each row of the result? E.g.: row_no | column1 | column2 | ... ---+-+-+ ... 1 | datum11 | datum12 | ... 2 | datum21 | datum22 | ... ... | ... | ... | ... I didn't find anything in the docs. TIA, Zoltan -- Kov\'acs, Zolt\'an [EMAIL PROTECTED] http://www.math.u-szeged.hu/~kovzol ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] enumerating rows
Use the "serial" column type. create table myTable (row_no serial,column1 varchar(10),column2 varchar(20)); HTH, Poul L. Christiansen On Wed, 11 Apr 2001, Kovacs Zoltan wrote: > Maybe a trivial question, maybe it's foreign from SQL, I'dont know... > How to add a column which stands for the row number in each row of the > result? E.g.: > > row_no | column1 | column2 | ... > ---+-+-+ ... > 1 | datum11 | datum12 | ... > 2 | datum21 | datum22 | ... >... | ... | ... | ... > > I didn't find anything in the docs. > > TIA, Zoltan > > -- > Kov\'acs, Zolt\'an > [EMAIL PROTECTED] > http://www.math.u-szeged.hu/~kovzol > ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(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] Re: \i command
I don't know if you've resolved your problem, but if not, you could try deleting and recreating the indexes on the table... Najm Hashmi wrote: > Joel Burton wrote: > > > On Wed, 11 Apr 2001, Najm Hashmi wrote: > > > > > Hi All, > > > From pgsql, I try to insert data in table using the \i command. Nothing > > > takes place and after this command pgsql is hung... i cant use it any more. > > > The same command works with all other tables but this is only one I am having > > > problem with. > > > I have attached my file with message. Could someone help me out here what is > > > the reason for this behaviour. > > > > Works just fine for me (Pg7.1 RC3, Linux). > > > > Can you do manual inserts into the table? > > Can you insert just a few records using \i? > > Can you vacuum the table? > > > > -- > > Joel Burton <[EMAIL PROTECTED]> > > Director of Information Systems, Support Center of Washington > > > > ---(end of broadcast)--- > > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > Hi, No I can't even do manaul insert on that particular table. This is really > weird. Could this table's internals be corrupted? > One more thing, if I run the same file on another db it works just fine. I am > just wondering what is wrong with the structure. > Thank you for replying. Anyone else has an idea about it > Regards. > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] enumerating rows
> Use the "serial" column type. Unfortunately it's not what I expect. Assume that I have an arbitrary "SELECT expr1 as column1, expr2 as column2, ..." which gives column1 | column2 | ... +-+- ... ..data.. I would like to get the same result with the only plus column row_no: row_no | column1 | column2 | ... ---+-+-+- ... 1 | ..data.. 2 | . with a new SELECT statement: "SELECT ?, expr1 as column1, expr2 as column2, ...". What to write instead of ?? TIA, Zoltan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [SQL] enumerating rows
I was searching for the same thing, I couldn't found it though :( -Original Message- From: Kovacs Zoltan [mailto:[EMAIL PROTECTED]] Sent: woensdag 11 april 2001 16:37 To: Poul L. Christiansen Cc: [EMAIL PROTECTED] Subject: Re: [SQL] enumerating rows > Use the "serial" column type. Unfortunately it's not what I expect. Assume that I have an arbitrary "SELECT expr1 as column1, expr2 as column2, ..." which gives column1 | column2 | ... +-+- ... ..data.. I would like to get the same result with the only plus column row_no: row_no | column1 | column2 | ... ---+-+-+- ... 1 | ..data.. 2 | . with a new SELECT statement: "SELECT ?, expr1 as column1, expr2 as column2, ...". What to write instead of ?? TIA, Zoltan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(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] enumerating rows
Kovacs Zoltan wrote:
>> Use the "serial" column type.
>Unfortunately it's not what I expect. Assume that I have an arbitrary
>"SELECT expr1 as column1, expr2 as column2, ..." which gives
>
>column1 | column2 | ...
>+-+- ...
>..data..
>
>
>I would like to get the same result with the only plus column row_no:
>
>row_no | column1 | column2 | ...
>---+-+-+- ...
> 1 | ..data..
> 2 |
>.
>
>with a new SELECT statement: "SELECT ?, expr1 as column1, expr2 as
>column2, ...". What to write instead of ??
Here is a method which is fairly cumbersome, but will do what you want.
(Whether what you want is useful, is another matter. The row numbers
have no meaning except to delineate which row is printed after which; they
bear no relation to their order in the table.)
Create the C code shown in the attachment.
Compile it (the example shown is for Linux, see the programmer's manual for
how to do it on other systems):
gcc -fpic -c rowno.c
gcc -shared -o rowno.so rowno.o
In the database, create functions as shown (remember to change
the directory from /tmp!):
CREATE FUNCTION reset_row() RETURNS int4
AS '/tmp/rowno.so' LANGUAGE 'C';
CREATE FUNCTION row_no() RETURNS int4
AS '/tmp/rowno.so' LANGUAGE 'C';
Now you can use the function:
bray=# select row_no() as row,id,name from person;
row | id | name
--++---
1 | 11 | Mr Graham Love (Director)
2 | 12 | AILEEN BROWN
...
but you have to do this in between queries:
bray=# select reset_row();
because the numbers don't reset themselves:
bray=# select row_no() as row,id,name from person;
row | id | name
---++---
6015 | 11 | Mr Graham Love (Director)
6016 | 12 | AILEEN BROWN
...
#include "postgres.h"
#include "fmgr.h"
static int32 row = 0;
PG_FUNCTION_INFO_V1(row_no);
Datum
row_no()
{
row += 1;
PG_RETURN_INT32(row);
}
PG_FUNCTION_INFO_V1(reset_row);
Datum
reset_row()
{
row = 0;
PG_RETURN_INT32(row);
}
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Is any one of you in trouble? He should pray. Is
anyone happy? Let him sing songs of praise. Is any one
of you sick? He should call the elders of the church
to pray over him...The prayer of a righteous man is
powerful and effective." James 5:13,14,16
---(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] enumerating rows
> Here is a method which is fairly cumbersome, but will do what you want. > (Whether what you want is useful, is another matter. The row numbers > have no meaning except to delineate which row is printed after which; they > bear no relation to their order in the table.) Thanks, Oliver! Are you sure there is no other (more convenient) solution? I don't think this is a part of the SQL standard but it could be a PostgreSQL extension. Hm? Regards, Zoltan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Making SELECT COUNT(seed) FROM fast
Hi all. I have a table with about 5 million rows in it. I need to be able to get the exact number of rows in the table at runtime. So I tried the following: xxx=> explain select count(seed) from mytable; NOTICE: QUERY PLAN: Aggregate (cost=103152.27..103152.27 rows=1 width=4) -> Seq Scan on mytable(cost=0.00..89756.42 rows=5358342 width=4) EXPLAIN xxx=> Actually executing this query takes about 2 minutes on a P3-800MHz machine with 512MB of RAM. I have an index on the seed table, and I have done VACUUM ANALYZE on the table after inserting the rows. Is there any way I can get this to be fast? Thanks. Gerald. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Making SELECT COUNT(seed) FROM fast
Gerald Gutierrez writes: > I have a table with about 5 million rows in it. I need to be able to get the > exact number of rows in the table at runtime. So I tried the following: > > xxx=> explain select count(seed) from mytable; > NOTICE: QUERY PLAN: > > Aggregate (cost=103152.27..103152.27 rows=1 width=4) > -> Seq Scan on mytable(cost=0.00..89756.42 rows=5358342 width=4) > Actually executing this query takes about 2 minutes on a P3-800MHz machine > with 512MB of RAM. > > I have an index on the seed table, and I have done VACUUM ANALYZE on the > table after inserting the rows. Is there any way I can get this to be fast? For a count of all rows you necessarily need to visit all rows (at least in this implementation), so an index is of no use. For a sequential scan with little computation involved this really comes down to pure hardware speed. You might be able to speed it up a little by using count(*) instead. Note that there's a semantic difference, because count(seed) doesn't count the rows where seed is null, which is probably not what you intended anyway. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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] enumerating rows
Kovacs, Oliver, First, thank you Oliver for the nice C program for this purpose. If there doesn't turn out to be another method, it shoudl og in the postgresql.org site. However, Postgresql does have an internal row count for query results. Otherwise LIMIT and OFFSET would not work. It seems like there should be some way to access this internal row count. -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]
Re: [SQL] enumerating rows
Kovacs Zoltan wrote: >> Here is a method which is fairly cumbersome, but will do what you want. >> (Whether what you want is useful, is another matter. The row numbers >> have no meaning except to delineate which row is printed after which; they >> bear no relation to their order in the table.) >Thanks, Oliver! Are you sure there is no other (more >convenient) solution? I don't think this is a part of the SQL standard but >it could be a PostgreSQL extension. Hm? I believe Oracle has an inbuilt feature to do this; I don't know about any other database. Nor do I know if any other databases can accommodate user-defined functions. But what is the actual use of this feature? Why do you need it? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Is any one of you in trouble? He should pray. Is anyone happy? Let him sing songs of praise. Is any one of you sick? He should call the elders of the church to pray over him...The prayer of a righteous man is powerful and effective." James 5:13,14,16 ---(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] Re: enumerating rows
On Wed, 11 Apr 2001, Kovacs Zoltan wrote: > > Here is a method which is fairly cumbersome, but will do what you want. > > (Whether what you want is useful, is another matter. The row numbers > > have no meaning except to delineate which row is printed after which; they > > bear no relation to their order in the table.) > Thanks, Oliver! Are you sure there is no other (more > convenient) solution? I don't think this is a part of the SQL standard but > it could be a PostgreSQL extension. Hm? If you don't need the numbers in PostgreSQL, but in the output, could you pipe your query results through `cat -b`, which will add line numbers? -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(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] DB porting questions...
Title: RE: [SQL] DB porting questions... Hi, Jeff, >> 1) >> I have a table: >> create table a ( >> t timestamp not null, >> ... >> ); >> >> I'm thinking that I can define a.t as not null default=now(). But >> will >> this >> work? That is, will it update a.t when I modified a given record? You need to set the default, but you have to use now() in (single) quotes, otherwise all records will use the time that the CREATE statement was executed: t timestamp not null default 'now()', ...or something close. It's in the docs somewhere too. >> 2) >> I have another table: >> create table b ( >> id int not null AUTO_INCREMENT, >> ... >> ); >> >> To reproduce this behavior, I believe I need to use a sequence. The >> problem >> is that I have a lot of data to import into this table. How do I >> import the old data without colliding with the new sequence numbers? What you do is create the id column of type SERIAL, then import your data, and then immediately afterwards, use setval() to update the current value of the sequence to one more than the highest value that you imported. The syntax for setval() is in the docs. During the import of your data, the sequence will not be used for incrementing the id, as it's only used as the default, not if you actually provide a value. Cheers... MikeA _ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __
Re: [SQL] DB porting questions...
Mike,
You'll be overjoyed to know that both of your questions have simple
answers.
> 1)
> I have a table:
> create table a (
> t timestamp not null,
> ...
> );
>
> I'm thinking that I can define a.t as not null default=now(). But
> will this
> work? That is, will it update a.t when I modified a given record?
No. Defaults only take effect when you INSERT a record, and only if you
don't supply a value. Thus, a.t will be updated with the time each new
record was added. If you want the time a record was modified, you need
to add an update trigger to the table that auto-updates the t field
whenever other changes are made.
See the development documentation for information on writing triggers.
>
>
> 2)
> I have another table:
> create table b (
> id int not null AUTO_INCREMENT,
> ...
> );
>
> To reproduce this behavior, I believe I need to use a sequence. The
> problem
> is that I have a lot of data to import into this table. How do I
> import the
> old data without colliding with the new sequence numbers?
Not a problem at all. Sequence numbers are merely defaults, and may be
overridden by a specific insert. Thus:
1. Create the id field as type SERIAL.
2. Insert your records into the new table, including the ID value.
3. Crank up the SERIAL sequence to the number of the highest ID present:
SELECT SETVAL('b_id_seq',10315);
-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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: DB porting questions...
On Wed, 11 Apr 2001, Diehl, Jeffrey wrote:
> Hi all,
>
> I'm in the final stages of migrating from mysql to postgres and have a few
> more questions...
>
> 1)
> I have a table:
> create table a (
> t timestamp not null,
> ...
> );
>
> I'm thinking that I can define a.t as not null default=now(). But will this
> work? That is, will it update a.t when I modified a given record?
>
>
> 2)
> I have another table:
> create table b (
> id int not null AUTO_INCREMENT,
> ...
> );
>
> To reproduce this behavior, I believe I need to use a sequence. The problem
> is that I have a lot of data to import into this table. How do I import the
> old data without colliding with the new sequence numbers?
1)
DEFAULT values only apply when *adding* a record, not modifying it, so,
no, "DEFAULT now()" (or more portably, DEFAULT CURRENT_TIMESTAMP) won't
change on updates. (I can't imagine any database that does do this for
DEFAULT values!)
If you want to track modifications, you want a trigger to watch for
updates. Look in /contrib/spi/moddatetime for help.
At my org, our important tables have
CREATE TABLE ... (
...
addby varchar(32) not null default current_user,
addat timestamp not null default current_timestamp,
chgby varchar(32) not null default current_user,
chgat timestamp not null default current_timestamp
);
and then add the triggers to track change times/users.
2)
You can use a sequence directly, most people would simply say
CREATE TABLE b (
id SERIAL NOT NULL PRIMARY KEY
...
);
If you old data in, that's fine. You can set the start for the sequence
after the importing so that the sequence starts w/the first new number
with SELECT SETVAL('b_id_seq', xxx), where xxx is the number for it to
begin new id numbers.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[SQL] Timezone conversion
Hi, How can I do timezone conversions in PG? I looked on the docs and couldn't find how. I want to find the current time in another timezone. Thanks, -Roberto P.S: This type of function would be excellent on the cookbook (www.brasileiro.net). -- +| 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 Ad astra, per ardua nostra. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Calling plSQL functions
Hello All,
I have created a plSQL function with the "create function ..." which seems to
be ok and is just very simple.
I then wanted to load the function by:
psql -f test.sql -d trdata
which loads my function fine.
the problem is that I do not know how to call this function from the PLSQL
interpreter.
I tried "select reg_user('name','age');"
but it just gives me an error about an unexpected "select ..."
I am writing some plSQL functions and also some "C" interface functions that I
want to load and then call from with the interpreter.
Any ideas?
cheers,
Lonnie
__
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Calling plSQL functions
Lonnie, It may be that you have not linked the PL/pgSQL language in Postgres. It may be that you are putting a SELECT statement inside your function. It is hard to tell, becuase I am unclear on the problem youa re having, exactly. Here's some terminology to help clarify: PL/pgSQL is the extension of SQL written by Jan Wieck used to write functions in PostgreSQL. PSQL is the command-line interface tool for the PostgreSQL database. PL/SQL is a procedure-writing language for Oracle which does not work on PostgreSQL at all, although it was the inspiration for PL/pgSQL. Please re-state you difficulty, and I can probably help you. -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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
