Postgres Pro
Downloads
Home   >   mailing lists

Thread: Operator script error

Operator script error

From
Bartosz Dmytrak
Date:
Hi,
it looks like operator script is generated incorrectly:
eg.
-- Operator: Calc.@#(NONE, _text) -- Lack of quotation

-- DROP OPERATOR Calc.@#(NONE, _text); -- Lack of quotation

CREATE OPERATOR Calc.@#(
  PROCEDURE = """Calc"".""doSomething""", -- Wrong quotation
  RIGHTARG = _text);
COMMENT ON OPERATOR Calc.@#(NONE, _text) IS 'this is comment'; -- Lack of quotation


Schema name and function should be quoted properly:

-- Operator: "Calc".@#(NONE, _text)

-- DROP OPERATOR "Calc".@#(NONE, _text); 

CREATE OPERATOR "Calc".@#(
  PROCEDURE ="Calc"."doSomething",
  RIGHTARG = _text);
COMMENT ON OPERATOR "Calc".@#(NONE, _text) IS 'this is comment';


Regards,
Bartek

Re: Operator script error

From
Guillaume Lelarge
Date:
On Mon, 2012-03-12 at 14:51 +0100, Bartosz Dmytrak wrote:
> Hi,
> it looks like operator script is generated incorrectly:
> eg.
> -- Operator: Calc.@#(NONE, _text) -- Lack of quotation
> 
> -- DROP OPERATOR Calc.@#(NONE, _text); -- Lack of quotation
> 
> CREATE OPERATOR Calc.@#(
>   PROCEDURE = """Calc"".""doSomething""", -- Wrong quotation
>   RIGHTARG = _text);
> COMMENT ON OPERATOR Calc.@#(NONE, _text) IS 'this is comment'; -- Lack of
> quotation
> 
> 
> Schema name and function should be quoted properly:
> 
> -- Operator: "Calc".@#(NONE, _text)
> 
> -- DROP OPERATOR "Calc".@#(NONE, _text);
> 
> CREATE OPERATOR "Calc".@#(
>   PROCEDURE ="Calc"."doSomething",
>   RIGHTARG = _text);
> COMMENT ON OPERATOR "Calc".@#(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

Re: Operator script error

From
Bartosz Dmytrak
Date:
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".@#(
  PROCEDURE = "Calc"."getDataRepository",
  RIGHTARG = _text);

COMMENT ON OPERATOR "Calc".@#(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@lelarge.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.@#(NONE, _text) -- Lack of quotation
>
> -- DROP OPERATOR Calc.@#(NONE, _text); -- Lack of quotation
>
> CREATE OPERATOR Calc.@#(
>   PROCEDURE = """Calc"".""doSomething""", -- Wrong quotation
>   RIGHTARG = _text);
> COMMENT ON OPERATOR Calc.@#(NONE, _text) IS 'this is comment'; -- Lack of
> quotation
>
>
> Schema name and function should be quoted properly:
>
> -- Operator: "Calc".@#(NONE, _text)
>
> -- DROP OPERATOR "Calc".@#(NONE, _text);
>
> CREATE OPERATOR "Calc".@#(
>   PROCEDURE ="Calc"."doSomething",
>   RIGHTARG = _text);
> COMMENT ON OPERATOR "Calc".@#(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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

Re: Operator script error

From
Guillaume Lelarge
Date:
On Tue, 2012-03-13 at 14:21 +0100, Bartosz Dmytrak wrote:
> Hi my script:

Thanks. I fixed the issue.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Re: Operator script error

From
Bartosz Dmytrak
Date:
Thank You for your time.

Regards,
Bartek


2012/3/13 Guillaume Lelarge <guillaume@lelarge.info>
On Tue, 2012-03-13 at 14:21 +0100, Bartosz Dmytrak wrote:
> Hi my script:

Thanks. I fixed the issue.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com


--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support