[SQL] Stored Procedures?
I read something about stored procedures in the Great Bridge User's Manual (Page 74 under "PG_LANGUAGE"). It is only mentioned briefly and there are no explanations of how it works. Can anybody let me know, how I can write a stored procedure and how to run it? Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Stored Procedures?
> I read something about stored procedures in the Great Bridge User's Manual
> (Page 74 under "PG_LANGUAGE"). It is only mentioned briefly and there are no
> explanations of how it works.
>
> Can anybody let me know, how I can write a stored procedure and how to run
> it?
Postgres doesn't have stored procedures in the same way that other
databases like oracle and sybase do. But it does have stored functions,
and they can be used in almost exactly the same way.
You create a function like this:
CREATE FUNCTION get_country(text) RETURNS text AS '
DECLARE
country_namecountry.name%TYPE;
country_key country.key%TYPE;
country_rec RECORD;
BEGIN
IF $1 ISNULL THEN
RETURN NULL;
END IF;
country_name = initcap($1);
SELECT INTO country_rec * FROM country
WHERE name = country_name;
IF FOUND THEN
RETURN country_rec.key;
END IF;
country_key := nextval(''country_key_seq'');
INSERT INTO country
VALUES (country_key, country_name);
RETURN country_key;
END;
' LANGUAGE 'plpgsql';
(You will need to load plpgsql support into your database. See the
createlang command for details.)
And you call it with SELECT, like this:
SELECT get_country('Zimbabwe');
Or from INSERT, like this:
INSERT INTO person (name, country_key)
VALUES ('Fred', get_country('Japan'));
The only difference between a function and a procedure is that a function
returns a value. If you don't need to return a value just pick a random
small result type (like bool, or int) return NULL, and ignore the return
value.
Usually I return a value even from procedural functions though just to
indicate if things went ok or not.
--
Tod McQuillin
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[SQL] Recursive select
> "Martin" == Martin Smetak <[EMAIL PROTECTED]> writes: Martin> Hi all! Anyone know if it's possible to make a recursive Martin> select from a table ? My problem: I got a table of "some Martin> categories" which all points to its parrent Martin> one(tree)...shown below. And I want to select all names of Martin> parrent categories of one child, lets say "fast[4]". Now Martin> I'm solving that with many SQL queries like : "SELECT Martin> main_id FROM cat WHERE id=4;"but I would like to Martin> optimize this. Martin> Anyone can help or point me to a way ?? The Openacs project has implemented a couple of different methods for handling tree queries. Check out these two threads: http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=eC&topic_id=11&topic=OpenACS http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=j6&topic_id=12&topic=OpenACS%204%2e0%20Design Regards, Dan Wickstrom ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] photos and OID's
Hi all, Thanks for the great product. We store photos in the db and serve them up from web servers. We're using java servlets on separate web server boxes that are load balanced. NFS is not an option for us (unless nothing else is available). This works with byte streams using SQL Server from MS. But, we don't want to use SQL Server any more, we want to use PostgreSQL. We need to get the photo data from the db without any file system access. In other words, we need the bytes, not the OID. So I read docs and found lo_import/lo_export. But that would only work for us over NFS and would cause major complications. lo_export won't cut it for us unless we make some major changes. Can I use the standard JDBC Blob type to retrieve the photos? What other options do I have? Thanks, Mark ---(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] ports in use question
I have started my pg installation using port 5444 (with the -p 5444 switch). Everything is working fine inside my installation with the internal ip number (a 192.168 number). I created a tunnel in my firewall to point to the linux box running pg with the port 5444 open. I can not seem to get to the server from outside. does anything else need to be opened to talk to the pg machine? is pg talking on some other port? thanks a million, Ted ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Select help
Hi, I am trying to do a 2 joins between 3 tables. ie) select , coalesce(TRR.ABC, SOC.ABC) as newABC, ... from A join (B join C on (..)) on (..)) as TRR left join (D join E on (..)) as SOC on (TRR.Field1=SOC.Field2) When I run this it says that there is an ambiguous field. Yes after the join for TRR and SOC they both contain a fields name ABC. How can I rename this field in the sql statement or how can I make it so the sql statement know that they are different. Please help.. .thanks -- Linh Luong Computalog Ltd. Software Developer Phone: (780) 464-6686 (ext 325) Email: [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
Re: [SQL] ports in use question
Two thoughts: 1. Did you start the postmaster with "-i" to allow non-local connections? 2. Did you add the remote host to the "pg_hba.conf" file? See: http://www.postgresql.org/idocs/index.php?client-authentication.html Kate postgresql wrote: > I have started my pg installation using port 5444 (with the -p 5444 > switch). Everything is working fine inside my installation with the > internal ip number (a 192.168 number). I created a tunnel in my > firewall to point to the linux box running pg with the port 5444 open. I > can not seem to get to the server from outside. > > does anything else need to be opened to talk to the pg machine? is > pg talking on some other port? > > thanks a million, > > Ted > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- = Katherine (Kate) L. Collins Senior Software Engineer/Meteorologist Weather Services International (WSI Corporation) 900 Technology Park Drive Billerica, MA 01821 EMAIL: [EMAIL PROTECTED] PHONE: (978) 262-0610 FAX: (978) 262-0700 http://www.intellicast.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: where's ALTER TABLE table DROP [ COLUMN ] column???
Bruno Boettcher wrote: > > Hello! > > as far as i can tell, i can add columns to a table, but can't remove > them later on. > > is this true? My thought would be that somebody must have written an automated version of select [all columns except named] from [named table] into droptemp; drop [named table]; select * from droptemp into [named table]; Would this be available or easily written as a "rule" ?? Could anyone familiar with the code comment? -- Keith Gray ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
