如何通过记录作为PL/pgSQL函数的参数? [英] How to pass a record as parameter for PL/pgSQL function?

问题描述

我一直在线寻找答案，但找不到.

I keep looking for this answer online but I cannot find it.

我正在尝试通过PL/pgSQL函数传递一条记录.我尝试了两种方式.

I am trying to pass one record over a PL/pgSQL function. I tried it in two ways.

第一路:

CREATE OR REPLACE FUNCTION translateToReadableDate(mRecord dim_date%ROWTYPE) RETURNS void AS $$

那是输出:

psql:requestExample.sql:21: ERROR:  syntax error at or near "%"
LINE 1: ... FUNCTION translateToReadableDate(mRecord dim_date%ROWTYPE) ...
                                                             ^

第二种方式:

CREATE OR REPLACE FUNCTION translateToReadableDate(mRecord RECORD) RETURNS void AS $$

有输出

psql:requestExample.sql:21: ERROR:  PL/pgSQL functions cannot accept type record

请问有人知道该怎么做吗?

Someone does know how to do this please ?

CREATE OR REPLACE FUNCTION translateToReadableDate(mRecord dim_date) RETURNS void AS $$

    BEGIN

    SELECT dim_day.name || ' (' || dim_day_in_month.id || ') ' || dim_month.name   || 'is the ' || dim_week.id || ' week of the year. ' AS "Une phrase", dim_quarter.id, dim_year.id
    FROM dim_date dd
     JOIN dim_day ON dd.day_id = dim_day.day_id
     JOIN dim_day_in_month ON dd.day_in_month_id = day_in_month.day_in_month_id
     JOIN dim_week ON dd.week_id = dim_week.week_id
     JOIN dim_month ON dd.month_id = dim_month.month_id
     JOIN dim_quarter ON dd.quarter_id = dim_quarter.quarter_id
     JOIN dim_year ON dd.year_id = dim_year.year_id
    WHERE dd.day_id = mRecord.day_id
     AND dd.day_in_month_id = mRecord.day_in_month_id
     AND dd.week_id = mRecord.week_id
     AND dd.month_id = mRecord.month_id
     AND dd.quarter_id = mRecord.quarter_id
     AND dd.year_id = mRecord.year_id;

    END;
    $$ LANGUAGE plpgsql;

推荐答案

尝试一下:

CREATE OR REPLACE FUNCTION translateToReadableDate(mRecord dim_date) RETURNS void AS $$

dim_date必须是一个表.

dim_date must be a table.

好吧，现在我真的很困惑.

Ok, now I'm really really confused.

1. 日期应该是一列，而不是表格.我不明白您为什么要创建一个带有日期值的表.
2. 您可以使用to_char设置日期格式没有问题.阅读以下内容:数据类型格式化功能，以了解如何使用.您创建的函数没有任何意义.
3. 您正在输出PL/pgSQL吗?格式化不应该由中间层完成吗?您应该只从数据库中返回一个日期.

1. A date should be a column, not a table. I can't understand why would you create a table with date values.
2. You can format dates no problem with to_char. Read this: Data Type Formatting Functions to learn how to. That function you created makes zero sense.
3. Are you outputting PL/pgSQL? Shouldn't the formatting be done by the middle tier? You should just return a Date from the database.

最后，我建议阅读PL/pgSQL 手册.有很多好东西.

Lastly, I would recommend reading the PL/pgSQL Manual. There's lots of good stuff in there.

这篇关于如何通过记录作为PL/pgSQL函数的参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！