4

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

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

Fist way :

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

That is the ouput :

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

Second way : 

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

And there is the output

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;
Spredzy
  • 4,982
  • 13
  • 53
  • 69

1 Answers1

2

Try this:

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

dim_date must be a table.

EDIT:

Ok, now I'm really really confused.

  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.

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

gcores
  • 12,376
  • 2
  • 49
  • 45
  • When I did what you told me, it did compile but I got a weird error #SELECT translateToReadableDate((SELECT * FROM dim_date LIMIT 1)); ERROR: subquery must return only one column LINE 1: SELECT translateToReadableDate((SELECT * FROM dim_date LIMIT... ^ So it doesn't take a record as parameter. Thank you for helping me. – Spredzy Oct 07 '10 at 09:34
  • translateToReadableDate returns void and you're using it in a Select? What exactly are you doing? It doesn't make much sense. The function does take a record as a parameter I'm sure of that, it how you're using that's giving you the problem. – gcores Oct 07 '10 at 09:46
  • translateToReadableDate(mRecord)doesn't return anything true. It's just a kind of elaborated echo - I agree with you it might be not the best way - Inside it I just to a SELECT so I can see the output. Do you get what I am trying to do? – Spredzy Oct 07 '10 at 10:08
  • Not really, no. Everything you're doing is a bit weird. A table called dim_date, a translateToReadableDate function, how you're using it... You know you can format dates in PL/pgSQL right?.And why are you doing Select translateToReadableDate(...)? If you want to test the function just call the function. What you're doing is very confusing. – gcores Oct 07 '10 at 10:22
  • I put my query on my question. So I was calling translateToReadableDate() so I could display datas the way I wanted it. If this is not the good way to do it please inform me I am currently learning pl/pgsql and would like to start with right basics. – Spredzy Oct 07 '10 at 10:41
  • Thank again. But date are in the way I want them to be. I am implementing a snowflake schema http://en.wikipedia.org/wiki/Snowflake_schema for a further use with OLAP server that's why date are made like this. – Spredzy Oct 07 '10 at 12:03
  • Can't say it makes sense what you're doing, but to each it's own. You still don't need that function as there is a to_date function that can do what you want. Check the Data Type Formatting Functions. – gcores Oct 07 '10 at 12:15