Re: Operator script error

Hi my script:
CREATE OR REPLACE FUNCTION "Calc"."getDataRepository"(a text[])
RETURNS numeric AS
$BODY$
DECLARE
/*
ARRAY STRUCTURE
Metric_Name, Date, Value_Type

Value_Type:
1. N - Numerator
2. D - Denominator
*/

vRetValue "DataRepository"."tblDataRepository"."NumeratorValue"%TYPE;
vMetricName "Dictionary"."tblDataMetrics"."MetricName"%TYPE;
vRowDate "DataRepository"."tblDataRepository"."RowDate"%TYPE;
vDataMetricId "Dictionary"."tblDataMetrics"."DataMetricId"%TYPE;
BEGIN
vMetricName := a[1];
vRowDate := a[2]::timestamp(0);

SELECT "DataMetricId" INTO vDataMetricId
FROM "Dictionary"."tblDataMetrics"
WHERE "MetricName" = vMetricName;

CASE
WHEN a[3] = 'N' THEN
SELECT "NumeratorValue" INTO vRetValue
FROM "DataRepository"."tblDataRepository"
WHERE "DataMetricId" = vDataMetricId AND "RowDate" = vRowDate;
WHEN a[3] = 'D' THEN
SELECT "DenominatorValue" INTO vRetValue
FROM "DataRepository"."tblDataRepository"
WHERE "DataMetricId" = vDataMetricId AND "RowDate" = vRowDate;
ELSE
vRetValue := NULL;
END CASE;

RETURN vRetValue;
END
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION "Calc"."getDataRepository"(text[])
OWNER TO postgres;

CREATE OPERATOR "Calc"(dot)(at)#(
PROCEDURE = "Calc"."getDataRepository",
RIGHTARG = _text);

COMMENT ON OPERATOR "Calc"(dot)(at)#(NONE, _text) IS '
Usage Example:
SET LOCAL search_path = "Calc";
SELECT @# (''{"MY_METRIC","2012-01-01","N"}'')
';

Regards,
Bartek

2012/3/12 Guillaume Lelarge <guillaume(at)lelarge(dot)info>

> On Mon, 2012-03-12 at 14:51 +0100, Bartosz Dmytrak wrote:
> > Hi,
> > it looks like operator script is generated incorrectly:
> > eg.
> > -- Operator: Calc(dot)(at)#(NONE, _text) -- Lack of quotation
> >
> > -- DROP OPERATOR Calc(dot)(at)#(NONE, _text); -- Lack of quotation
> >
> > CREATE OPERATOR Calc(dot)(at)#(
> > PROCEDURE = """Calc"".""doSomething""", -- Wrong quotation
> > RIGHTARG = _text);
> > COMMENT ON OPERATOR Calc(dot)(at)#(NONE, _text) IS 'this is comment'; -- Lack
> of
> > quotation
> >
> >
> > Schema name and function should be quoted properly:
> >
> > -- Operator: "Calc"(dot)(at)#(NONE, _text)
> >
> > -- DROP OPERATOR "Calc"(dot)(at)#(NONE, _text);
> >
> > CREATE OPERATOR "Calc"(dot)(at)#(
> > PROCEDURE ="Calc"."doSomething",
> > RIGHTARG = _text);
> > COMMENT ON OPERATOR "Calc"(dot)(at)#(NONE, _text) IS 'this is comment';
> >
>
> Can you give me the script that allowed you to create the operator? The
> script should contain the function definition. Without it, it would be a
> lot of work to reproduce your issue.
>
>
> --
> Guillaume
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>
>
> --
> Sent via pgadmin-support mailing list (pgadmin-support(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support
>