- 论坛徽章:
- 0
|
昨晚上没事摆弄postgresql,觉得使用stored procedure还是挺别扭的啊,怎么在insert, update, delete之后返回row_count?我昨天试了一下,没成功,只能暂时让它返回boolean了,付上脚本,谁有空帮我看看,谢谢。
--------------------------------------------------------
--
-- PostgreSQL database dump
--
\connect - postgres
SET search_path = public, pg_catalog;
--
-- TOC entry 2 (OID 16981)
-- Name: area; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE area (
id integer NOT NULL,
name character varying(32)
);
--
-- TOC entry 3 (OID 16981)
-- Name: area; Type: ACL; Schema: public; Owner: postgres
--
REVOKE ALL ON TABLE area FROM PUBLIC;
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE area TO GROUP users;
--
-- TOC entry 6 (OID 16985)
-- Name: plpgsql_call_handler (); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler
AS '$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAGE c;
--
-- TOC entry 5 (OID 16986)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner:
--
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
--
-- TOC entry 7 (OID 1699
-- Name: sp_area_select (integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION sp_area_select (integer) RETURNS SETOF area
AS 'declare rec record; v_id alias for $1; begin select into rec id, name from area where id=v_id; return next rec; return; end;'
LANGUAGE plpgsql;
--
-- TOC entry 8 (OID 16999)
-- Name: sp_area_select (); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION sp_area_select () RETURNS SETOF area
AS 'declare rec record; begin for rec in select * from area order by id loop return next rec ; end loop; return; end;'
LANGUAGE plpgsql;
--
-- TOC entry 9 (OID 17006)
-- Name: sp_area_insert (integer, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION sp_area_insert (integer, character varying) RETURNS boolean
AS 'declare v_id alias for $1; v_name alias for $2; begin insert into area values (v_id, v_name); return found; end;'
LANGUAGE plpgsql;
--
-- TOC entry 10 (OID 17010)
-- Name: sp_area_delete (integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION sp_area_delete (integer) RETURNS boolean
AS 'declare v_id alias for $1; begin delete from area where id = v_id; return found; end;'
LANGUAGE plpgsql;
--
-- TOC entry 11 (OID 17011)
-- Name: sp_area_update (integer, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION sp_area_update (integer, character varying) RETURNS boolean
AS 'declare v_id alias for $1; v_name alias for $2; begin update area set name = v_name where id=v_id; return found; end;'
LANGUAGE plpgsql;
--
-- TOC entry 4 (OID 16983)
-- Name: area_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY area
ADD CONSTRAINT area_pkey PRIMARY KEY (id); |
|