The Open Source Swiss Army Knife
|
|||||
| | |||||
| sub-categories and articles | |
| --->create new sub-category | |
 a_a.sql | |
| add
SET search_path = public; CREATE or replace FUNCTION a_b (text) RETURNS text AS ' DECLARE -- Declare aliases for function arguments. location_in ALIAS FOR $1; | |
| a_b.sql | |
| add
CREATE or replace FUNCTION a_b() RETURNS int AS ' DECLARE -- Declare aliases for function arguments. | |
| a_c.sql | |
| add
CREATE or replace FUNCTION a_c(text) RETURNS int AS ' DECLARE -- Declare aliases for function arguments. | |
| a_d.sql | |
| add
--address=# select * from GetRows('address'); --ERROR: A column definition list is required for functions returning RECORD create or replace function GetRows(text) returns setof record as ' declare | |
| a_e.sql | |
| add
create type a_e_type as ( id int ); CREATE or replace FUNCTION a_e(text) | |
| a_f.sql | |
| sql function %|| syntax
CREATE or replace FUNCTION mod_log.top_hits(text) returns setof getpass_type AS ' SELECT uri, client_ip FROM mod_log.apachelog WHERE uri like ''%'' || $1 || ''%'' ' LANGUAGE 'sql'; | |
| a_g.sql | |
| add
CREATE or replace FUNCTION setpass(text, text) returns INTEGER AS ' DECLARE BEGIN | |
| acl_list.sql | |
| add
-- PostgreSQL access control functions by Andew Hammond with the help of Tim Goodaire. -- * source: acl_admin.pgsql -- * AutoDoc: acl_admin.html -- $Id: acl_admin.sql,v 1.6 2004/10/05 15:32:19 ahammond Exp $ -- | |
| b.sql | |
| add
CREATE OR REPLACE FUNCTION Hello(VARCHAR) RETURNS VARCHAR AS ' DECLARE pName ALIAS FOR $1; BEGIN RETURN ''Hello '' || pName; | |
| b_1.sql | |
| add
I'm wondering if there is an elegant way to add simple behaviours to stored Postgres functions. 1. First, what I want to get here is a function which orders their results columns in ASC at one time and in DESC next time it is called. Something like that: CREATE FUNCTION sort_swap(integer) RETURNS SETOF atype AS ' | |
| create_tables.sql | |
| add
CREATE or replace FUNCTION test.create_tables(integer) RETURNS integer AS ' DECLARE input integer; tablename text; | |
| cs.sql | |
| add
CREATE FUNCTION cs ( ) RETURNS boolean AS ' DECLARE -- Declare an integer. s INTEGER; | |
| cs2.sql | |
| add
CREATE FUNCTION cs2 ( ) RETURNS boolean AS ' DECLARE -- Declare an integer. s INTEGER; | |
| double_salary.sql | |
| add
CREATE FUNCTION get_pass(passwd) RETURNS text AS 'SELECT $1.pass AS salary;' LANGUAGE 'sql'; | |
| errors.sql | |
| add
create type mod_log.error_type as (uri character varying(1536), hits bigint, status int ); create or replace function mod_log.errors(interval) returns setof mod_log.error_type as ' declare | |
| for_in_execute.txt | |
| add
Dany De Bontridder wrote: > If you have a look to the following code, I'm trying to work around > the limitation about execute, which doesn't accept "select into". http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN The results from SELECT commands are discarded by EXECUTE, and SELECT | |
| for_loop.sql | |
| add
drop function for_loop(integer); CREATE FUNCTION for_loop (integer) RETURNS varchar AS ' DECLARE master_id_in alias for $1; master_count integer; | |
| function1.sql | |
| plpgsql function sets not null value
-- This function takes a table and column and will set the column -- to NOT NULL. -- -- $Id$ -- | |
| function2.sql | |
| This function takes a table and column and will set the column -- to allow NULLs.
-- This function takes a table and column and will set the column -- to allow NULLs. -- -- $Id$ -- | |
| get_p.sql | |
| add
create or replace function get_pass(text) returns setof joe.passwd_type as ' declare when_for alias for $1; | |
| get_pass.sql | |
| add
CREATE or replace FUNCTION get_pass(text) RETURNS setof joe.passwd_type as ' declare abc alias for $1; row passwd_type%ROWTYPE; | |
| hostname.sql | |
| add
drop function hostname(); --drop type stats_type; --drop table stats; --create table stats (cnt integer,uri varchar(1536), cr varchar(1536)); --create type stats_type as (cnt integer,uri varchar(1536), cr varchar(1536)); | |
| hostname2.sql | |
| add
drop function hostname(); --drop type stats_type; --drop table stats; create table stats2 (cnt integer,uri varchar(1536), cr varchar(1536)); --create table stats (cnt integer,uri varchar(1536), cr varchar(1536)); | |
| hostname3.sql | |
| add
-- generate list of client referers from mod_pgsqllog table -- assumes: -- 1. name of website=www.sirfsup.com -- 2. mod_pgsqllog is logging to the apachelog table -- by joe speigle, joe.speigle@jkhl.us | |
| indexes_show.sql | |
| add
select * from pg_indexes where tablename = 'xxx'; | |
| installlanguage.txt | |
| sql commands to install plpgsql as a languages
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE as '/usr/local/pgsqlcvs/lib/plpgsql.so' LANGUAGE 'C'; | |
| matviews.sql | |
| raise exception, name datatype, execute
drop table source; create table source ( document_source varchar(100) ); insert into source values ('http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html'); | |
| mazurek.sql | |
| add
drop function mazurek(integer); CREATE FUNCTION mazurek (integer) RETURNS varchar AS ' DECLARE master_id_in alias for $1; master_count integer; | |
| mazurek_test.sql | |
| add
select mazurek(1); --test SELECT count(*) as master_count FROM master; SELECT count(*) as detail_count FROM detail; | |
| newtimestamp.sql | |
| plpgsql function returns a new timestamp value
CREATE FUNCTION NewTimeStamp () RETURNS OPAQUE AS ' BEGIN NEW.ModifiedTimeStamp := ''now''; RETURN NEW; END; ' | |
| one.sql | |
| like ''%'' || uname_in ||''%'';
CREATE or replace FUNCTION one(text) RETURNS text AS ' declare uname_in alias for $1; return_holder xxx.filename%type; | |
| raise_notice.sql | |
| add
CREATE FUNCTION child_numer( INT8, INT8, INT8) RETURNS INT8 AS' DECLARE num ALIAS FOR $1; den ALIAS FOR $2; child ALIAS FOR $3; | |
| raise_performl.sql | |
| add
보낸 사람: Patrick Refondini <patrick.refondini@pobox.com> 받는 사람: pgsql-general@postgresql.org 제목: [SPAM] [GENERAL] RAISE function misuse ? 날짜: Mon, 03 Apr 2006 17:44:18 +0200 ((화) 00:44 KST) Hi, | |
| reg_exp.sql | |
| add
create or replace function osregexp(varchar,varchar) returns boolean as ' select $1 ~ $2; 'language sql immutable; SELECT osregexp('windows9x','win.*9x'); -- returns 't' | |
| rowtype1.sql | |
| cursors looping looping through a cursor pl pgsql
CREATE or replace FUNCTION rowtype(text) RETURNS text AS ' declare uname_in alias for $1; table_row table1%ROWTYPE; | |
| rowtype2.sql | |
| cursors rowtypes concatenating fields of rowtype to dynamically generate a return result
-- will not work with large result set as will be returned as one row -- means that header will occupy a LOT of space on output CREATE or replace FUNCTION rowtype(text) RETURNS text AS ' declare | |
| schema.sql | |
| add
create schema mod_log authorization mod_log; grant usage on schema mod_log to mod_log; REVOKE ALL ON SCHEMA mod_log FROM PUBLIC; | |
| set.sql | |
| add
-- drop table constraint_table cascade; create table constraint_table ( "id" bit(64) UNIQUE , set_values varchar UNIQUE); insert into constraint_table values (B'0000000000000000000000000000000000000000000000000000000000000001','one'); insert into constraint_table values (B'0000000000000000000000000000000000000000000000000000000000000010','two'); | |
| setreturningFunctions.sql | |
| loops without cursor prints out results from select from function
create function SqlDepartmentSalaries() returns setof holder as ' select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid ' language 'sql'; | |
| timestamp.sql | |
| add
drop function NewTimeStamp(); CREATE FUNCTION NewTimeStamp () RETURNS timestamp AS ' declare new timestamp; BEGIN | |
| top_hits.sql | |
| add
create type mod_log.top_hits_type as (uri character varying(1536), hits bigint); create or replace function mod_log.top_hits(interval) returns setof mod_log.top_hits_type as ' declare | |
| trigger_basic.sql | |
| add
CREATE TABLE t_text (id int4, tstamp timestamp); CREATE OR REPLACE FUNCTION enterdate() RETURNS trigger AS ' DECLARE BEGIN NEW.tstamp := now(); | |
| trigger_return.sql | |
| add
-- Date: Thu, 25 Mar 2004 12:36:58 -0600 (CST) -- From: Anoop Rajendra <axr3845@omega.uta.edu> -- Subject: [GENERAL] Problem with inserting into a triggerable table; -- My code is shown below - CREATE OR REPLACE FUNCTION update_tv_prefs() RETURNS trigger AS ' | |
| trigger_return2.sql | |
| add
CREATE OR REPLACE FUNCTION update_xxx() RETURNS trigger AS ' BEGIN NEW.mod_date=now(); RETURN NEW; END; | |
| trigger_return3.sql | |
| add
drop table mod_perl_webapp.file_forum; create table mod_perl_webapp.file_forum ( id serial, username varchar(20), email varchar(40), | |
| triggers_show.sql | |
| add
select c.relname as "Table", t.tgname as "Trigger Name", t.tgconstrname as "Constraint Name", t.tgenabled as "Enabled", t.tgisconstraint as "Is Constraint", cc.relname as "Referenced Table", p.proname as "Function Name" from pg_trigger t, pg_class c, pg_class cc, pg_proc p | |
| type_check.sql | |
| add
-- Select Cast('asdf' as numeric) -- ERROR: invalid input syntax for type numeric create or replace function check_numeric(varchar) returns bool as $$ declare i numeric; | |
| --->upload your article | |
User submitted category site links |
|
(None) | |
-->submit a page from your site dealing with pg_plpgsql to the sirfsup! web directory for listing | |