Chinaunix

标题: stored procedure的返回值问题 [打印本页]

作者: joint 时间: 2003-04-11 10:42
标题: stored procedure的返回值问题
昨晚上没事摆弄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);

作者: joint 时间: 2003-04-13 11:28
标题: stored procedure的返回值问题
在手册的19.5.5找到下面的说明, 还是没成功返回ROW_COUNT

GET DIAGNOSTICS var_integer = ROW_COUNT;

作者: joint 时间: 2003-04-13 13:21
标题: stored procedure的返回值问题
是我弄错了,把diagnostics 写成了 diagnostatics

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)