How to Execute PostgreSQL Commands Inside Unix Shell Scripts. 10 Steps to Creating a Function In PostgreSQL Using PLpgSQL. It's actually fairly easy to create a function using PLpgSQL, especially if you are coming from a database background like Oracle or DB2.
Both have procedural languages that look a lot like PLpgSQL. I'll go ahead and show you the code for a very basic function and then I'll explain the steps individually. CREATE OR REPLACE FUNCTION func_1() RETURNS CHAR VARYING(25) AS $$ DECLARE local_char_var CHAR(30); BEGIN SELECT datname INTO local_char_var FROM pg_database LIMIT 1; RETURN local_char_var; END; $$ LANGUAGE plpgsql; Ok. Now we'll go through it line by line: CREATE OR REPLACE FUNCTION func_1() - This line creates and names the function. Updating One Table from Another - Full Table Scan. Twice in the last few weeks I've been faced with the problem of updating one table with values from another.
I admittedly don't do this very often, but given that tables are the central objects in a database I would have thought that this would be easier to do. But, like so many database things, the SQL required turns out to be both unintuitive and database-specific. So, after surveying a half-dozen or so databases, here's what I came up with. Assume two tables: target table T and source table S. Both have two columns: ColA, the key and ColB, the value. Oracle, DB2 and SQLite I find this form to be especially counterintuitive, as it operates row-wise - note the EXISTS condition in the WHERE clause - whereas most operations require you to think in terms of sets.
UPDATE T SET ColA = (SELECT ColA FROM S WHERE ColB = T.ColB) WHERE EXISTS (SELECT 1 FROM S WHERE ColB = T.ColB) Microsoft SQL Server UPDATE T SET ColA = S.ColA FROM T, S WHERE T.ColB = S.ColB. Index.jsp?topic=%2Fsqlp%2Frbafysqltrig. Example code for using Triggers in Postgres. A Brief Real-world Trigger Example. A Brief Real-world Trigger Example Created 2003-03-13 by Richard Huxton (dev@archonet.com) Version: First Draft - treat with caution This is a real-world example, showing how you can use the plpgsql procedural language to build a trigger function to enforce integrity beyond that which foreign keys can offer you.
The tables have been simplified to the minimum required for this example, but represent real tables for a real project. The Database We have a set of products, each of which has a certain type. This gives us a table structure like: product (pr_id, pr_type) server (svr_id, pr_type) server_products (svr_id, pr_id) We can use foreign keys to make sure that server_products have a valid svr_id and pr_id but if we want to enforce the type of a product we need to check two tables, because what matters is that the pr_type from product matches the corresponding one in server. Solution 1 (in an ideal world...) Solution 2 (well, it's not normal...) Triggers in PostgreSQL – Quick Example! « coding tiger. I tried my first hand on PostgreSQL Triggers yesterday.
Actually my current project needs some triggers, but I decided to try with a test table and create a trigger for it. There are two tables, ‘addressbook’ and ‘phonebook’. The ‘addressbook’ is the main table in which data is to be entered. The structure of ‘addressbook’ is as follows. Table "public.addressbook" Column | Type | Modifiers ----------+------------------------+---------------------------------------------------------- id | integer | not null default nextval('addressbook_id_seq'::regclass) name | character varying(100) | address1 | character varying(100) | address2 | character varying(100) | address3 | character varying(100) | phonenum | character varying(15) | Indexes: "addressbook_pkey" PRIMARY KEY, btree (id) "addressbook_name_key" UNIQUE, btree (name) The ‘phonebook’ is another table with just stores ‘name’ and ‘phonenum’ fields of the ‘addressbook’. Yes, the trigger has worked.