DB 复习

点击打开链接json

create function one()
	returns integer as
	$$
	select 1 as result;
	$$
	language sql;

create function add_int(integer,integer)
	returns integer as
	$$
	select $1 + $2;
	$$
	language sql;


-- t4
create function write_to_temp(id integer, name varchar(20)) 
	RETURNS integer AS
	$$
	insert into temp(id, name) VALUES($1, $2)
	returning id;
	$$
	language sql VOLATILE;

create or replace function delete_from_temp(id integer)
	returns integer as
	$$
	delete from temp where id = $1
		returning id;
	$$
	language sql VOLATILE;

create or replace function update_temp(id integer,name varchar(20))
	returns integer as
	$$
	update temp set name = $2 where id = $1
		returning id;
	$$
	language sql VOLATILE;

create or replace function select_temp(id integer)
	returns varchar(20) as
	$$
	select name from temp where id = $1;
	$$
	language sql VOLATILE;

-- t1
create function say(varchar(20))
	returns varchar(20) as
	$$
	select $1;
	$$
	language sql;

-- t5
create type combo_nation as (nationkey integer,nationname char(25));
create or replace function get_nations(region_name varchar(20))
	returns setof combo_nation as
	$$
	select n.n_nationkey, n.n_name from nation n, region r
	where r.r_name = $1 and r.r_regionkey = n.n_regionkey;
	$$
	language sql;

select count(o_orderkey)
from orders
where cast (o_orderdate as varchar(20)) like '1997%';

create or replace function get_order_count(varchar(20))
	returns bigint as
	$$
	select count(o_orderkey)
	from orders
	where cast (o_orderdate as varchar(20)) like $1||'%';
	$$
	language sql;

-- t2
create or replace function mypower(a double precision, b double precision)
	returns double precision as
	$$
	select a^b
	$$
	language sql;

-- t3
create or replace function mymod(a integer, b integer)
	returns integer as
	$$
	select a%b
	$$
	language sql;

-- t6
create function square(numeric[2],numeric)
	returns numeric[2] as
	$$
	select array[$1[0]^2,$1[1]^2];
	$$
	language sql IMMUTABLE;

create function mean(numeric[2])
	returns numeric as
	$$
	select ($1[0]^2+$1[1]^2)/2
	$$
	language sql IMMUTABLE;

create aggregate square_mean (numeric[2])
(
    sfunc = square,
    stype = numeric[],
    finalfunc = mean 
);

-- t7
create type mytype as (id integer,name char(25), age integer);
create function setmytype(integer, char(25),integer)
	returns id as
	$$
	set copy.id = $1,
	$$

Python 连接数据库执行操作

import psycopg2
conn = psycopg2.connect(database="testdb", user="postgres", password="password", host="127.0.0.1", port="5432")
print "Opened database successfully"
cur = conn.cursor()
cur.execute(“…")
conn.commit()
cur.close()
conn.close()