PostgreSQL 存储过程（函数）的正确语法是什么？

发布于 2024-11-29 01:26:30 字数 734 浏览 1 评论 0原文

我正在尝试在 PostgreSQL 中编写两种类型的存储过程。据我了解，Postgre 只有函数。我想知道是否有人可以看一下我的代码并提供指导。另外，我不熟悉命令的间距/新行。

第一个函数需要接受用户的输入并将其添加到表格中。 假设我们有一个名为“Car”的表，其属性为“model”和“year”。这是将新车添加到表中的正确存储函数吗？

CREATE OR REPLACE FUNCTION
    addto_car(model IN Car.model%type, year IN Car.year%type)
RETURNS
    void
AS $$
BEGIN
    INSERT INTO Car VALUES(model, year);
END;
$$ LANGUAGE plpgsql; (#Is this correct? I'm using postgresql 9)

---------- 正在进行中的代码 函数 1

CREATE OR REPLACE FUNCTION
    addto_car(In model Car.model%type, IN year Car.year%type)
AS $$
BEGIN
    INSERT INTO Car VALUES(model, year);
END;
$$ LANGUAGE plpgsql;

现在可以了！（将值型号和年份插入到汽车中）。

原文

I'm trying to write two types of stored procedures in PostgreSQL. From what I understand Postgre only has functions. I was wondering if someone can take a look at my code and offer pointers. Also, I'm am not familiar whether with the spacing/new lines of commands.

The first function needs to take input from user and add it onto a table.
Suppose we have a table name "Car" with attributes "model" and "year".
Will this be a correct stored function to add a new car to the table?

CREATE OR REPLACE FUNCTION
    addto_car(model IN Car.model%type, year IN Car.year%type)
RETURNS
    void
AS $
BEGIN
    INSERT INTO Car VALUES(model, year);
END;
$ LANGUAGE plpgsql; (#Is this correct? I'm using postgresql 9)

---------- Work in Progress code
Function 1

CREATE OR REPLACE FUNCTION
    addto_car(In model Car.model%type, IN year Car.year%type)
AS $
BEGIN
    INSERT INTO Car VALUES(model, year);
END;
$ LANGUAGE plpgsql;

This now works! (inserts values model and year into Car).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

明媚殇 2024-12-06 01:26:30

从官方文档

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | COST execution_cost
    | ROWS result_rows
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...
    [ WITH ( attribute [, ...] ) ]

您会在那里找到答案，也许，在这个过程中学到两三个有用的东西。

您可能对 RETURNS TABLE 构造特别感兴趣。

From the Official Documentation

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | COST execution_cost
    | ROWS result_rows
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...
    [ WITH ( attribute [, ...] ) ]

You will find your answer there and, maybe, learn two or three useful things on the process.

You might be particularly interested in the RETURNS TABLE construct.

回复收藏 0 原文

~没有更多了~